CODEWITHSUNDEEP
sql-server
Jmaurier
Jmaurier

Reputation: 847

Group by month and year given yyyy-mm-dd as a date

I have a list of incidents and dates along with them, yyyy-mm-dd then a category.

I need to order by the category (speeding tickets, DUIs, etc...) for each month in a year.

This is what the two columns look like

2002-12-28  No Driver License
2002-12-28  Hazardous Violations
2002-12-28
2002-12-28  Speeding    
2002-12-28  DUI
2002-12-28  Reckless Driving
2002-12-28      
2002-12-28  Speeding

And here is what I have so far

SELECT
     [Date]
    ,[categoryId]   
     = (SELECT tertiary FROM [MSDAS].[dbo].[activity_categories] AS C 
        WHERE C.categoryId = A.categoryId)
    ,[value]

FROM [MSDAS].[dbo].[hp_activities] AS A 
    JOIN [MSDAS].[dbo].[workdays] AS W ON W.workdaysId = A.workdaysId

WHERE W.date IS NOT NULL 
    AND W.date >= '2002/1/1'

ORDER BY Date

Upvotes: 1

Views: 4865

Answers (2)

jkavalik
jkavalik

Reputation: 1316

http://sqlfiddle.com/#!9/badf7/1

SELECT sum(id),
       category,
       year(t) y,
       month(t) m
FROM table
GROUP BY category, y, m;

I did prepare it before you added sample data, so categories are numerical (good idea to normalize that anyway, to have a separate table of categories and keep only category_id here)

Thats what I understood by group by month and year.

Edit: answered before question was retagged from mysql to sql server

Upvotes: 0

Yelwes
Yelwes

Reputation: 36

SELECT innerSelect.dateMonth
, innerSelect.dateYear

FROM (SELECT (MONTH(yourTable.Date)) AS dateMonth
           , YEAR(yourTable.Date) AS dateYear
        FROM yourTable) AS innerSelect

GROUP BY innerSelect.dateMonth, innerSelect.dateYear

Upvotes: 2

Related Questions