Multiple counts in one SQL query with grouping

Question

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)

Oleksandr Fedorenko · Accepted Answer · 2013-06-16 08:16:53Z

3

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)

edited Jun 16, 2013 at 8:16

answered Jun 16, 2013 at 8:04

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

4b0 · Accepted Answer · 2013-06-16 07:33:56Z

1

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)

edited Jun 16, 2013 at 7:33

answered Jun 16, 2013 at 6:41

4b0

22.4k30 gold badges97 silver badges143 bronze badges

1 Comment

Daqqaq Over a year ago

Shree, the query above did not worked. I got this error (Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'as'.)

Daqqaq · Accepted Answer · 2013-06-17 04:29:22Z

0

I have managed to solve it, below is the query

select  COUNT(CASE WHEN Month(Closedate)=Month(Opendate) then 1 else null end) AS closed,COUNT(*) AS Opened
from incidents
Group by Year(Opendate), Month(Opendate)
Order by Year(Opendate), Month(Opendate)

answered Jun 17, 2013 at 4:29

Daqqaq

411 gold badge1 silver badge3 bronze badges

1 Comment

Andriy M Over a year ago

You can probably omit the else null bit. I think most SQL products allow that, defaulting the missing ELSE clause to ELSE NULL. However, isn't this essentially same as @Alexander Fedorenko's answer?

Collectives™ on Stack Overflow

Multiple counts in one SQL query with grouping

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related