SQL总计pr组

Question

我需要计算销售的总和“在过去6周内”公关周。从今天起我需要：

• 从今天起一周后的销售总额。
• 两周前的销售总额。
• 3周前的销售总额。
• ..。直到6周前。

我有以下查询：

select retailerid,stylenumber,size,length,
    case when date > dateadd(WEEK,-1,GETDATE()) then SUM(quantity) else 0 end as w_1,
    case when date > dateadd(WEEK,-2,GETDATE()) then SUM(quantity) else 0 end as w_2,
    case when date > dateadd(WEEK,-3,GETDATE()) then SUM(quantity) else 0 end as w_3,
    case when date > dateadd(WEEK,-4,GETDATE()) then SUM(quantity) else 0 end as w_4,
    case when date > dateadd(WEEK,-5,GETDATE()) then SUM(quantity) else 0 end as w_5,
    case when date > dateadd(WEEK,-6,GETDATE()) then SUM(quantity) else 0 end as w_6        
    from someSalesTable
    left join dimdate on datekey = fk_date
    group by retailerid,stylenumber,size,length

错误是“日期”不是在分组子句中，而是如何解决这个问题？

简单(但很难看)的修复方法是进行6个不同的查询:/，但我需要在一个结果中提供这些信息，因为它将与另一个CTE连接。

Answer 1

您应该SUM()整个CASE WHEN表达式，而不仅仅是quantity

select retailerid,stylenumber,size,length,
    SUM(case when date > dateadd(WEEK,-1,GETDATE()) then (quantity) else 0 end) as w_1,
    SUM(case when date > dateadd(WEEK,-2,GETDATE()) then (quantity) else 0 end) as w_2,
    SUM(case when date > dateadd(WEEK,-3,GETDATE()) then (quantity) else 0 end) as w_3,
    SUM(case when date > dateadd(WEEK,-4,GETDATE()) then (quantity) else 0 end) as w_4,
    SUM(case when date > dateadd(WEEK,-5,GETDATE()) then (quantity) else 0 end) as w_5,
    SUM(case when date > dateadd(WEEK,-6,GETDATE()) then (quantity) else 0 end) as w_6        
    from someSalesTable
    left join dimdate on datekey = fk_date
    group by retailerid,stylenumber,size,length

Answer 2

如果在使用GROUP BY子句时有条件，则date列应该处于聚合函数中：

select retailerid, stylenumber, size, length,
       sum(case when [date] > dateadd(WEEK, -1, GETDATE()) then quantity else 0 end) as w_1,
       . . .
from someSalesTable left join 
     dimdate 
     on datekey = fk_date
group by retailerid, stylenumber, size, length;