主页 / dba / 问题 / 303738
Accepted
Ganesh_55
Asked: 2021-12-10 03:12:41 +0800 CST

根据列值获取总和行

  • 772

我有这样的数据

在此处输入图像描述

我想要下面的结果。

在此处输入图像描述

sql-server-2008-r2

2 个回答

  1. Best Answer

    尝试这个。

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

UNION ALL

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

  2. 你可以用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函数告诉我们列是否已被分组。

    • 0

相关问题