我一直在编写一个查询，以便按年对销售额进行分组，其他列包含季度销售额、每个季度的增长百分比、季度销售额的季度环比变化以及中的最后一列中的年度销售总额。
我运行了以下查询：

WITH Sales_By_Quarter AS
(
    SELECT
        DATEPART(YEAR, OrderDate) AS [Year],
        DATEPART(QUARTER, OrderDate) AS [Quarter],
        SUM(TotalDue) AS [Quarterly Sales],
        SUM(TotalDue) - LAG(SUM(TotalDue)) OVER (PARTITION BY DATEPART(YEAR, OrderDate) ORDER BY DATEPART(QUARTER, OrderDate)) AS [Change]
    FROM Sales.SalesOrderHeader
    GROUP BY DATEPART(YEAR, OrderDate), DATEPART(QUARTER, OrderDate)
),
Annual_Sales AS
(
    SELECT
        [Year],
        SUM([Quarterly Sales]) AS [Total Annual Sales],
        SUM([Quarterly Sales]) - LAG(SUM([Quarterly Sales])) OVER (ORDER BY [Year]) AS [Annual Growth]
    FROM Sales_By_Quarter
    GROUP BY [Year]
)
-- SELECT * FROM Annual_Sales;

SELECT
    Sales_By_Quarter.[Year],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 1 THEN Sales_By_Quarter.[Quarterly Sales] END) AS [Q1],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 1 THEN (Sales_By_Quarter.[Quarterly Sales]/Annual_Sales.[Total Annual Sales]*100) END) as [Annual %],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 1 THEN Sales_By_Quarter.[Quarterly Sales] END) AS [4 to 1],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 2 THEN Sales_By_Quarter.[Quarterly Sales] END) AS [Q2],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 2 THEN Sales_By_Quarter.[Quarterly Sales]/Annual_Sales.[Total Annual Sales]*100 END) as [Annual %],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 2 THEN Sales_By_Quarter.[Change] END) AS [1 to 2],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 3 THEN Sales_By_Quarter.[Quarterly Sales] END) AS [Q3],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 3 THEN Sales_By_Quarter.[Quarterly Sales]/Annual_Sales.[Total Annual Sales]*100 END) as [Annual %],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 3 THEN Sales_By_Quarter.[Change] END) AS [2 to 3],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 4 THEN Sales_By_Quarter.[Quarterly Sales] END) AS [Q4],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 4 THEN Sales_By_Quarter.[Quarterly Sales]/Annual_Sales.[Total Annual Sales]*100 END) as [Annual %],
    SUM(CASE WHEN Sales_By_Quarter.[Quarter] = 4 THEN Sales_By_Quarter.[Change] END) AS [3 to 4],
    Annual_Sales.[Total Annual Sales]
FROM Sales_By_Quarter
JOIN Annual_Sales ON Sales_By_Quarter.[Year] = Annual_Sales.[Year]
GROUP BY Sales_By_Quarter.[Year], Annual_Sales.[Total Annual Sales], Annual_Sales.[Annual Growth]
ORDER BY Sales_By_Quarter.[Year];

除了4到1列之外，我在所有列中都得到了正确的值。我需要一些帮助来修复此查询。