对季度数据进行分组并在选择中计算 SUM

Question

我有一个包含以下列的 SQL Server 销售数据表：

销售ID，销售日期，销售金额，项目数，项数组

我正在尝试做的是编写一个查询，该查询将列出自 2017 年以来的季度销售数据，返回的每一行代表一个季度。我已通过以下查询完成此操作：

SELECT
  DATEPART(YEAR, saleDate) [YEAR],
  DATEPART(QUARTER, saleDate) [QUARTER],
  COUNT(1) [Total Sales],
  FORMAT(SUM(saleAmount), 'C', 'en-us') [Total Sales Revenue]
FROM
  tbl_sales
WHERE
  saleDate > '2017-01-01'
GROUP BY
  DATEPART(YEAR, saleDate), DATEPART(QUARTER, saleDate)
ORDER BY
  1,2

这给了我每行四分之一的结果，这很棒。我正在努力解决的是，我还想在结果中有一个列，显示每次销售商品数量的加权平均值。我要计算的加权平均值是：

SUM(saleAmount/[Total Sales Revenue]*numberOfItems)

有没有办法用一个查询来完成这一切？每当我尝试这样做时，我都会收到无效的列名错误：

SELECT
  DATEPART(YEAR, saleDate) [YEAR],
  DATEPART(QUARTER, saleDate) [QUARTER],
  COUNT(1) [Total Sales],
  FORMAT(SUM(saleAmount), 'C', 'en-us') [Total Sales Revenue],
  SUM(saleAmount/[Total Sales Revenue]*numberOfItems)
FROM
  tbl_sales
WHERE
  saleDate > '2017-01-01'
GROUP BY
  DATEPART(YEAR, saleDate), DATEPART(QUARTER, saleDate)
ORDER BY
  1,2
----
Invalid column name 'Total Sales Revenue'

任何帮助将不胜感激。谢谢！

Answer 1

试试这个。需要在 cte 中首先计算总数：

;WITH cte_sum (
SELECT
  DATEPART(YEAR, saleDate) [YEAR],
  DATEPART(QUARTER, saleDate) [QUARTER],
  SUM(saleAmount) AS TotalSales
FROM
  tbl_sales
WHERE
  saleDate > '2017-01-01'
GROUP BY
  DATEPART(YEAR, saleDate), DATEPART(QUARTER, saleDate)
)
SELECT t.[YEAR],t.[QUARTER],
  FORMAT(MAX(t.TotalSales), 'C', 'en-us') [Total Sales Revenue],
  SUM(s.saleAmount/t.TotalSales*s.numberOfItems) AS WeightedAvg
FROM tbl_sales s
INNER JOIN cte_sum t
ON DATEPART(YEAR, s.saleDate)=t.[YEAR]
AND DATEPART(QUARTER, s.saleDate)=t.[QUARTER]
GROUP BY t.[YEAR],t.[QUARTER]