sql - 使用汇总的 SQL 查询

Question

这是我的查询

select X.Memo, X.Month, Format(sum(X.[Total Sales S$]),'#,0.00') as [Rental Sales $]
    , Format(sum(X.[Net GP S$]),'#,0.00') as [Rental GP $]
from Data X
group by X.Month,X.Memo

这是我的结果：

预期的结果应该是这样的：

是否可以在 SQL 中做到这一点？可以帮我查询吗？

标签： sqlsql-server

Answer 1

一种方法是使用 case 语句并获得您提到的结果。您可以以类似的方式添加所需的列并获取输出。

SELECT MONTH,
  sum(CASE WHEN Memo = 'Rental' THEN sales$ END) Rental_$,
  sum(CASE WHEN Memo = 'Rental' THEN GP$ END) Rental_GP,
  sum(CASE WHEN Memo = 'Sales' THEN sales$ END) Sales_$,
  sum(CASE WHEN Memo = 'Sales' THEN GP$ END) Sales_GP
FROM tablea
GROUP BY MONTH

SELECT MONTH,
  Format(sum(CASE WHEN Memo = 'Rental' THEN (X.[Total Sales S$]) END), '#,0.00') Rental_$,
  Format(sum(CASE WHEN Memo = 'Rental' THEN (X.[Net GP S$]) END), '#,0.00') Rental_GP,
  Format(sum(CASE WHEN Memo = 'Sales' THEN (X.[Total Sales S$]) END), '#,0.00') Sales_$,
  Format(sum(CASE WHEN Memo = 'Sales' THEN (X.[Net GP S$]) END), '#,0.00') Sales_GP,
  Format((sum(CASE WHEN Memo = 'Rental' THEN (X.[Total Sales S$]) END) + sum(CASE WHEN Memo = 'Sales' THEN (X.[Total Sales S$]) END)), '#,0.00') [Total Sales S$],
  Format((sum(CASE WHEN Memo = 'Rental' THEN (X.[Net GP S$]) END) + sum(CASE WHEN Memo = 'Sales' THEN (X.[Net GP S$]) END)), '#,0.00') [Total GP S$]
FROM DATA X
GROUP BY MONTH WITH ROLLUP
ORDER BY SUM(X.MN) ASC