Group data by hour - 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 2008 Forums

Transact-SQL (2008)

Group data by hour

Author

Topic

kdefilip
Starting Member

2 Posts

Posted - 2011-05-15 : 10:39:31

Hi
I'm trying to summarize data by hour. I need to convert the datetime to just the hour and count all items that were generated per each hour. The below does not work correctly as it groups by hour/minute even though datetime has been converted to the two digit hour:
SELECT CONVERT(VARCHAR(8) , item_time , 108) AS Hour,COUNT(prdkey) AS count
FROM Item_summary
WHERE Item_date BETWEEN '03/01/2011' AND '03/01/2011'
GROUP BY Item_time
ORDER BY Hour ASC

The returned data looks like this

09, 1
09, 3
09, 2
10, 1
10, 4
etc.

Using the above example, I need the return to look like this:
09, 6
10, 5

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-15 : 10:56:17

You can use the hour function (or datepart function) as follows:


SELECT hour(item_time) AS Hour,COUNT(prdkey) AS count
FROM Item_summary
WHERE Item_date BETWEEN '03/01/2011' AND '03/01/2011'
GROUP BY hour(item_time)
ORDER BY Hour ASC

If your data would span more than one day and you want to separate them by days as well, you would need to add the day also as in


SELECT day(item_time) as [Day],hour(item_time) AS Hour,COUNT(prdkey) AS count
FROM Item_summary
WHERE Item_date BETWEEN '03/01/2011' AND '03/02/2011'
GROUP BY day(item_time),hour(item_time)
ORDER BY Hour ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources