根據列值獲取總和行

我有這樣的數據

我想要下面的結果。

試試這個。

Select [Commodity], [Business Entities], [Quantity]
from MyTable

UNION ALL

SELECT [Commodity], 'TOTAL', SUM([Quantity])
from MyTable
group by [Commodity]
order by [Commodity]

你可以用GROUPING SETS這個

SELECT
 Commodity,
 [Business Entities] = CASE WHEN GROUPING([Business Entities]) = 1 THEN 'Total' ELSE [Business Entities] END,
 Quantity = SUM(Quantity)
FROM YourTabe
GROUP BY GROUPING SETS (
   (Commodity, [Business Entities]),
   (Commodity)
)
ORDER BY
 [Commodity],
 GROUPING([Business Entities]),
 [Business Entities];

該GROUPING函式告訴我們列是否已被分組。

引用自：https://dba.stackexchange.com/questions/303738