温馨提示:本文翻译自stackoverflow.com,查看原文请点击:mysql - How do I count the total of number of participants in this way?
mysql sql sql-server

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

发布于 2020-03-31 23:37:28

如果我在表中有这些值:

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

查看更多

提问者
Sneezy
被浏览
6
Yogesh Sharma 2020-01-31 20:06

你可以用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;

最新帖子

最新文章