获取年初至今的总和

debugcn 发表于 Dev

用户名

前两列效果很好，但我不知道如何获得第三列的结果。请告诉我如何在第三栏中显示根据星期显示的年初至今的数据。请提供协助。

select "Builder","Traffic", sum(cast("Traffic" as int)) as YTD
from trafficdatapcr
where "Week" = '2016-12-11'
group by "Builder","Traffic"

样本数据：

     Week          Builder             Traffic

   2016-12-11      Macys                 100
   2016-10-11      Bloomingdales          15
   2016-08-11      Saks                   85
   2016-02-11      Cole Haan              95
   2015-12-25      Kroger                 65

我目前的结果：

 Builder          Traffic        YTD
 Macys              100           100

预期结果：

 Builder          Traffic        YTD
 Macys              100          100
 Saks               0             85
 Bloomingdales      0             15
 Cole Haan          0             95
 Kroger             0             65

希伯特

您的where子句消除了所需的记录，请使用case来有条件地显示所需星期的流量，而不是where子句

select "Builder"
     , case when "Week" = to_date('2016-12-11',YYYY-MM-DD') then "Traffic" else 0 end as "Traffic"
     , sum(cast("Traffic" as int)) as YTD
from trafficdatapcr
group by "Builder","Traffic"
Order by week Desc

确实有些奇怪，但是如果有人选择2016-10-11，那么YTD将是所有日期....所以也许您也想有条件地求和...

select "Builder"
     , case when "Week" = to_date('2016-12-11','YYYY-MM-DD') then "Traffic" else 0 end as "Traffic"
     , sum(case when "week"<=to_date('2016-12-11','YYYY-MM-DD') then cast("Traffic" as int) else 0 end) as YTD
from trafficdatapcr
group by "Builder","Traffic"
Order by week Desc

这条路

梅西百货将显示为0 0
Bloomingdales将是15 15

因此，第二个查询应该返回（假定日期为2016-10-11），但按正确的日期顺序（不知道您想要什么顺序）

 Builder          Traffic        YTD
 Macys              0              0
 Saks               0             85
 Bloomingdales      15            15
 Cole Haan          0             95
 Kroger             0             65

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。