Multiple counts in one SQL query with grouping
10,022
Solution 1
You can use single a SELECT statement with CASE expression
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)
Solution 2
Try:
SELECT
(SELECT COUNT(*) FROM Incidents) as Total ,
(SELECT COUNT(*) FROM Incidents WHERE (Month(Closedate)=MONTH(Opendate))) as Solved
FROM Incidents
Group by YEAR(Incidents.Opendate)
Author by
Daqqaq
Updated on June 07, 2022Comments
-
Daqqaq almost 2 years
I want to run a query to get a count of open incident and closed incidents grouped by year and month, the below query works fine without the grouping, but once I add the group it will not work!
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)