Reputation: 177
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
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