如果我在表中有这些值:
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
你可以用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;
谢谢,使用窗口功能解决了我的问题