CODEWITHSUNDEEP
sqlsql-servergroup-bysum
user2575604
user2575604

Reputation: 3

SQLSRV: group by one column and sum by month

I have a difficult query to do =)

the Stops table have the following columns

Id, Area, SubArea, Date, StopNumber, DownTime

I would like to have a result like this: (I'll try to describe..)

    Area, SubArea, SUM(StopNumber appened in january) as GEN,
     SUM(StopNumber appened in february) as FEB, etc..
FROM Stops
GROUP BY Area, SubArea

Could anyone help me? thank you :)

Upvotes: 0

Views: 1030

Answers (2)

Eggplant
Eggplant

Reputation: 2013

If you like the approach I suggested you in the comment, you could try something like this:

SELECT Area, SubArea, MONTH(Date) AS Month, SUM(StopNumber) AS NumStops
    FROM Stops
    GROUP BY Area, SubArea, MONTH(Date)

Upvotes: 1

Roland Jansen
Roland Jansen

Reputation: 2783

This should work:

SELECT Area, SubArea,
SUM(CASE Month(Date) WHEN 1 THEN 1 ELSE 0 END) AS Jan,
SUM(CASE Month(Date) WHEN 2 THEN 1 ELSE 0 END) AS Feb,
SUM(CASE Month(Date) WHEN 3 THEN 1 ELSE 0 END) AS Mar,
SUM(CASE Month(Date) WHEN 4 THEN 1 ELSE 0 END) AS Apr,
SUM(CASE Month(Date) WHEN 5 THEN 1 ELSE 0 END) AS May,
SUM(CASE Month(Date) WHEN 6 THEN 1 ELSE 0 END) AS Jun,
SUM(CASE Month(Date) WHEN 7 THEN 1 ELSE 0 END) AS Jul,
SUM(CASE Month(Date) WHEN 8 THEN 1 ELSE 0 END) AS Aug,
SUM(CASE Month(Date) WHEN 9 THEN 1 ELSE 0 END) AS Sep,
SUM(CASE Month(Date) WHEN 10 THEN 1 ELSE 0 END) AS Oct,
SUM(CASE Month(Date) WHEN 11 THEN 1 ELSE 0 END) AS Nov,
SUM(CASE Month(Date) WHEN 12 THEN 1 ELSE 0 END) AS [Dec]
FROM Stops
GROUP BY Area, SubArea

Upvotes: 0

Related Questions