CODEWITHSUNDEEP
sqlsql-serversql-server-2008
bibinmatthew
bibinmatthew

Reputation: 177

Group by week days and Month

I want to group data by week day and month. Say i want to find (sum of volume for all sundays in Januray) + (sum of volumes for all sundays in Feb) + ... What i did is like 

  select CONVERT(VARCHAR(10),date,111),
         DATENAME(weekday,date) as Weekday,
         DATEPART(month,date) as Month,
         DATEPART(year,date) as year,
         sum(volume)
    from traffic_data_replica
group by CONVERT(VARCHAR(10),date,111),
         DATENAME(weekday,date),
         DATEPART(month,date),
         DATEPART(year,date)
order by DATEPART(year,date),
         CONVERT(VARCHAR(10),date,111),
         DATEPART(month,date),
         DATENAME(weekday,date) ;

But this is not the desired result i want :(

Upvotes: 1

Views: 1644

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You are including the actual date in the group by (the CONVERT(VARCHAR(10),date,111)), so the day is the level of aggregation. Try something like this:

SELECT Year(date), 
       Month(date), 
       Datename(weekday, date) AS Weekday, 
       Sum(volume) 
FROM   traffic_data_replica 
GROUP  BY Year(date), 
          Month(date), 
          Datename(weekday, date) 
ORDER  BY Year(date), 
          Month(date), 
          Datename(weekday, date);

You can use datepart() instead of year() and month(). I just find the latter easier to read and type.

Upvotes: 3

Related Questions