求YTD与MTD销售总额的SQL查询报错问题求助

你的报错核心问题是窗口函数里直接引用了未被聚合或分组的原始列。原查询中，你用GROUP BY按orderdate、月份、年份分组，但窗口函数里的(unit_price*order_quantity)-discount_applied用的是sales表的原始字段，这些字段既没被包含在GROUP BY里，也没被聚合函数包裹，SQL引擎无法确定如何在分组后的行里处理这些字段，所以抛出错误。

修正后的查询语句 #

可以先通过子查询或CTE完成每日销售总额的聚合，再基于聚合结果计算YTD和MTD：

方式1：使用CTE

WITH daily_sales AS (
    SELECT 
        orderdate,
        DATENAME(month, orderdate) AS Mnth,
        YEAR(orderdate) AS YR,
        SUM((unit_price * order_quantity) - discount_applied) AS Total_Sales
    FROM sales
    GROUP BY orderdate, DATENAME(month, orderdate), YEAR(orderdate)
)
SELECT 
    orderdate,
    Mnth,
    YR,
    Total_Sales,
    SUM(Total_Sales) OVER (PARTITION BY YR ORDER BY orderdate) AS YTD,
    SUM(Total_Sales) OVER (PARTITION BY YR, Mnth ORDER BY orderdate) AS MTD
FROM daily_sales

方式2：使用子查询

SELECT 
    orderdate,
    Mnth,
    YR,
    Total_Sales,
    SUM(Total_Sales) OVER (PARTITION BY YR ORDER BY orderdate) AS YTD,
    SUM(Total_Sales) OVER (PARTITION BY YR, Mnth ORDER BY orderdate) AS MTD
FROM (
    SELECT 
        orderdate,
        DATENAME(month, orderdate) AS Mnth,
        YEAR(orderdate) AS YR,
        SUM((unit_price * order_quantity) - discount_applied) AS Total_Sales
    FROM sales
    GROUP BY orderdate, DATENAME(month, orderdate), YEAR(orderdate)
) AS daily_sales

修正逻辑说明 #

先通过内层查询完成按日期的聚合，把原始的单价、订单数量、折扣字段计算成每日的Total_Sales。在外层查询中，基于已经聚合好的每日销售额，用窗口函数分别按年份、年份+月份分组排序，计算累计的YTD（年初至今）和MTD（月初至今）值，这样就避免了引用未聚合原始字段的问题。

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