如何以这种方式计算参与者总数?

Question

如果我在表中有这些值：

Description | number of participants | GroupId
------------------------------------------
Alpha          1                       34
Beta           1                       34
Beta           3                       34
Beta           3                       34
Charlie        1                       34
Charlie        2                       34

如何查询以获取此结果？

Alpha   11 34
Beta    11 34
Beta    11 34
Beta    11 34
Charlie 11 34
Charlie 11 34

这应该表示同一 ID 的参与者总数

SQL查询我正在尝试：

SELECT description
,COUNT(1) AS Cnt
FROM Table1
GROUP BY description
UNION ALL
SELECT 'SUM' description
,COUNT(1)
FROM Table1

Answer 1

您可以将sum()与subquery一起使用：

SELECT description, COUNT(1) as Cnt
FROM Table1
GROUP BY description
UNION ALL
SELECT 'SUM', SUM(Cnt)
FROM (SELECT COUNT(1) AS Cnt
FROM Table1
GROUP BY description
) C;

但是，在SQL Server您可以使用rollup：

SELECT ISNULL(description, 'SUM') AS description, COUNT(1) as Cnt
FROM Table1
GROUP BY description WITH ROLLUP;'

根据示例输出，您正在寻找窗口功能：

select description, SUM(number_of_participants) OVER (PARTITION BY groupId), groupId
from Table1;

如果窗口函数不支持，您可以使用子查询：

select t.description, 
(select sum(t1.number_of_participants) 
from table1 t1 
where t1.groupid = t.groupid
), t.groupid 
from Table1 t;

Answer 2

如果您希望预期输出中提到的所有行的总和，则可以使用SUM() over()窗口函数。

SELECT description,
SUM(number_of_participants) OVER () as Total,
groupId 
from your_table.

输出：

+-------------+-------+---------+
| descritpion | total | groupId |
+-------------+-------+---------+
| Alpha       | 11    | 34      |
| Beta        | 11    | 34      |
| Beta        | 11    | 34      |
| Beta        | 11    | 34      |
| Charlie     | 11    | 34      |
| Charlie     | 11    | 34      |
+-------------+-------+---------+

如果需要每个描述的总和，请使用SUM() OVER (PARTITION BY )

SELECT description,
SUM(number_of_participants) OVER (partition by description) as Total,
groupId 
from your_table.

输出：

+-------------+-------+---------+
| descritpion | total | groupId |
+-------------+-------+---------+
| Alpha       | 1     | 34      |
| Beta        | 7     | 34      |
| Beta        | 7     | 34      |
| Beta        | 7     | 34      |
| Charlie     | 3     | 34      |
| Charlie     | 3     | 34      |
+-------------+-------+---------+

Answer 3

在 SQL Server 中，您可以使用GROUPING SETS：

SELECT COALESCE(description, 'SUM'), COUNT(1) as Cnt
FROM Table1
GROUP BY GROUPING SETS ( (description), () );