sql - 一个 SQL 查询中的多个计数与分组

Question

我想运行查询以获取按年和月分组的未解决事件和已关闭事件的计数，下面的查询在没有分组的情况下工作正常，但一旦我添加了组，它将无法工作!

SELECT (SELECT COUNT(*) AS Opened FROM Incidents) AS Total 
       (SELECT COUNT(*) AS Solved FROM Incidents WHERE (MONTH(Closedate)=MONTH(Opendate))) AS Solved
GROUP BY YEAR(Incidents.Opendate)

Answer 1

您可以使用带有 CASE 表达式的单个 SELECT 语句

SELECT YEAR(Incidents.Opendate) AS [Year], 
       MONTH(Incidents.Opendate) AS [Month], 
       COUNT(*) AS Total,
       SUM(CASE WHEN MONTH(Closedate) = MONTH(Opendate) THEN 1 ELSE 0 END) AS Solved
FROM Incidents
GROUP BY YEAR(Incidents.Opendate), MONTH(Incidents.Opendate)