Sum items by Day of Week - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum items by Day of Week

Author

Topic

Vergy39
Starting Member

15 Posts

Posted - 2013-09-06 : 12:36:36

I have an import that runs daily into a sql database. I would like to sum the records by the day of the week. For example, how many were imported on Sunday, Monday, Tuesday, etc. I can get the count by Import date, but I want to start summing all the Sundays, Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, and Saturdays in a month. I have seen a few threads on summing by Week ending, but having trouble using that info for my situation. Any assistance is greatly appreciated.

Thanks
David V

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 12:54:23

One of the two below. In the first, 0 corresponds to Monday, 1 to Tuesday and so on and 6 corresponds to Sunday

SELECT
	DATEDIFF(DAY,0,ImportDate)%7,
	SUM(ImportedQuantity)
FROM
	YourTable
GROUP BY
	DATEDIFF(DAY,0,ImportDate)%7;
	
SELECT
	DATENAME(weekday,ImportDate) AS Weekday,
	SUM(ImportedQuantity)
FROM
	YourTable
GROUP BY
	DATENAME(weekday,ImportDate)

Vergy39
Starting Member

15 Posts

Posted - 2013-09-06 : 15:28:57

Thanks James. This helped alot. I am wondering though if I could get the results to show for each Sunday in a month. For example, I have 4 Sundays in August, I would want to return the number imported for each Sunday, ie:
Date NumImported
8/4/2013 30265
8/11/2013 42569
8/18/2013 15623
8/25/2013 35263

Something like that. I can get this by just entering the dates in a where claus, but would like to get more dates by using between in a where clause. Here is what you gave me,

SELECT DATENAME(weekday, MyDate) AS Weekday, Count(MyDate) AS NumImport
FROM MyTable
Where MyDate Between '2013-08-01 00:01' AND '2013-09-05 23:59'
GROUP BY DATENAME(weekday, MyDate)
Order By DATENAME(weekday, MyDate)

It returns:
MyDate NumImport
Friday 109392
Monday 106300
Saturday 56233
Sunday 30934
Thursday 136542
Tuesday 113273
Wednesday 102435

Thanks for the help.

David V

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 15:55:55

Do you mean that you want to see only data imported on Sundays, or do you want to aggregate all the data imported upto each Sunday (thus aggregating the data for the 7 days) into one group?

If you just want to see the Sundays, add another condition to the where clause as in:

...
AND datediff(day,0,MyDate)%7 = 6

Vergy39
Starting Member

15 Posts

Posted - 2013-09-06 : 16:16:33

Thanks again for the quick reply, however, that Where claus just returns the sum from all the Sunday's that would be in the Between dates listed in the Where clause. I want it to return the individual Sundays within the between dates to compare the totals. IE:
Date
Sunday, 8/4/2013 6126
Sunday, 8/11/2013 6257
Sunday, 8/18/2013 5844
Sunday, 8/25/2013 6550
Sunday, 9/1/2013 6157
Total for All Sundays 30934

Again, I certainly appreciate your assistance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 17:06:30

I didn't quite follow what you want to do; can you give this a try:

SELECT  DATENAME(weekday, MyDate) AS Weekday ,
        COUNT(MyDate) AS NumImport
FROM    MyTable
WHERE   MyDate BETWEEN '2013-08-01 00:01'
               AND     '2013-09-05 23:59'
        AND DATEDIFF(DAY,0,MyDate)%7 = 6
GROUP BY GROUPING SETS (DATENAME(weekday, MyDate),())
ORDER BY DATENAME(weekday, MyDate)

Vergy39
Starting Member

15 Posts

Posted - 2013-09-06 : 18:01:04

I tried what you sent, but I got an error that stated "Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near 'SETS'." Looks like I put this thread in the worng forum. I have sql 2000. Sorry about that. However, I do appreciate your time.

Thanks
David V

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources