聚合函数搭配Group by异常：带InvoiceNo过滤的客户统计需求

修正后的查询语句 #

SELECT 
    SUM(Amt) AS Amnt,
    LineManager,
    Staffname,
    MONTH(date) AS month,
    FORMAT(date, 'dddd') AS [Day Name],
    COUNT(DISTINCT CASE 
        WHEN Order_type = 'SALES_ORDER' AND InvoiceNo != '0' 
        THEN customer 
        ELSE NULL 
    END) AS [Customercovered]
FROM SalesTrasncation Salest        
INNER JOIN Customer cus ON Salest.SITE = cus.customercode     
WHERE date BETWEEN '2022-11-24' AND '2022-11-24'       
GROUP BY LineManager, Staffname, MONTH(date), FORMAT(date, 'dddd');

调整要点 #

• 聚合函数写法修正：原写法把COUNT(DISTINCT)放在CASE分支里，属于聚合函数嵌套，数据库不支持。改成外层用COUNT(DISTINCT CASE ... END)，让数据库先判断每条记录是否符合条件，符合的返回对应客户ID，不符合返回NULL，最后统计唯一的非NULL客户数，完美避开嵌套报错。
• 精简分组字段：原GROUP BY里加了date和order_type，会导致结果按订单类型拆分，哪怕是同一天的数据也会分成多条。既然WHERE已经限定了单一日期，MONTH(date)和[Day Name]的结果是固定的，只保留必要的分组字段就能得到你要的聚合结果。
• 过滤逻辑优化：不符合条件时返回NULL而非0，因为COUNT会自动忽略NULL，这样统计的就是完全符合要求的客户数，不会把无效记录算进去。

单条结果的特殊处理 #

如果不需要按LineManager和Staffname分组，要纯单条汇总结果，直接去掉这两个字段的分组和查询：

SELECT 
    SUM(Amt) AS Amnt,
    MONTH(date) AS month,
    FORMAT(date, 'dddd') AS [Day Name],
    COUNT(DISTINCT CASE 
        WHEN Order_type = 'SALES_ORDER' AND InvoiceNo != '0' 
        THEN customer 
        ELSE NULL 
    END) AS [Customercovered]
FROM SalesTrasncation Salest        
INNER JOIN Customer cus ON Salest.SITE = cus.customercode     
WHERE date BETWEEN '2022-11-24' AND '2022-11-24'       
GROUP BY MONTH(date), FORMAT(date, 'dddd');

内容的提问来源于stack exchange，提问作者Bilal