sql - 当Case语句涉及日期列时如何处理聚合函数

Question

我正在计算 invoiceDate 与 currentDate，然后按客户对 Value/Amount 列分组求和，但其返回“invoiceDate 不包含在聚合函数或 GROUP BY 子句中”

select Customer     
        ,case
            when datediff(dd,InvoiceDate,getdate()) <=30 then sum(InvoiceBal1)
            else 0
        end as [Current]
        ,case
            when datediff(dd,InvoiceDate,getdate()) between 31 and 60 then sum(InvoiceBal1)
            else 0
        end as [30 Days]
from CusInvoice
group by Customer

标签： sqlsql-server

Answer 1

你想要条件聚合：

select Customer     
       sum(case when datediff(day, InvoiceDate, getdate()) <= 30
                then InvoiceBal1 else 0
           end) as balance_current
       sum(case when datediff(day, InvoiceDate, getdate()) between 31 and 60
                then InvoiceBal1 else 0
           end) as balance_30days
from CusInvoice
group by Customer