在SQL Server查询结果中显示子总数

SELECT 
    BCD.[EventID],
    M.TargetCategory,
    BCD.[RoundName],
    COUNT(CASE
             WHEN R.Status_Id <> 4
                THEN 1
                ELSE NULL
          END) AS [Outstanding Events]
FROM 
    [dbo].[Event_Details] BCD
LEFT JOIN 
    [dbo].[lkpTarget] M ON BCD.TargetID = M.TargetID
LEFT JOIN 
    [dbo].[EventComments] R ON BCD.EventID = R.[EventID]
GROUP BY 
    BCD.[EventID], M.TargetCategory, BCD.[RoundName]
HAVING 
    COUNT(CASE
             WHEN R.Status_Id <> 4
                THEN 1
                ELSE NULL
          END) > 0
ORDER BY 
    TargetCategory ASC;

I have the above query showing the [Outstanding Events] for each RoundName.

我有上面的查询，显示每个圆号的[未完成事件]。

Does anyone know the best way I can modify the above query such that I can show [Outstanding Events] sub totals grouped by TargetCategory and then an [Outstanding Events] grand total for the entire query result?

有没有人知道我可以修改上述查询的最佳方式，以便我可以显示按TargetCategory分组的[未完成事件]子总数，然后显示整个查询结果的[未完成事件]总计?

1 个解决方案

#1

Use grouping sets:

使用分组集:

GROUP BY GROUPING SETS ( (BCD.[EventID], M.TargetCategory, BCD.[RoundName]),
                         (BCD.[RoundName]),
                         ()
                       )