question

jhowe avatar image
jhowe asked

Group by specified time period

Hi all consider the following : CREATE TABLE #request (request INT , creation DATE) INSERT INTO #request ( request, creation ) VALUES ( 2, -- request - int '2010-06-06 14:56:27') -- registration - date INSERT INTO #request ( request, creation ) VALUES ( 5, -- request - int '2010-06-06 14:56:27') -- registration - date INSERT INTO #request ( request, creation ) VALUES ( 3, -- request - int '2010-06-06 14:56:27') -- registration - date INSERT INTO #request ( request, creation ) VALUES ( 7, -- request - int '2011-06-06 14:56:27') -- registration - date INSERT INTO #request ( request, creation ) VALUES ( 4, -- request - int '2011-06-06 14:56:27') -- registration - date INSERT INTO #request ( request, creation ) VALUES ( 6, -- request - int '2011-06-06 14:56:27') -- registration - date INSERT INTO #request ( request, creation ) VALUES ( 8, -- request - int '2012-06-06 14:56:27') -- registration - date INSERT INTO #request ( request, creation ) VALUES ( 2, -- request - int '2012-06-06 14:56:27') -- registration - date INSERT INTO #request ( request, creation ) VALUES ( 9, -- request - int '2012-06-06 14:56:27') -- registration - date SELECT * FROM #request request creation 2 2010-06-06 5 2010-06-06 3 2010-06-06 7 2011-06-06 4 2011-06-06 6 2011-06-06 8 2012-06-06 2 2012-06-06 9 2012-06-06 What I want to see is this 10 2010-06-06 17 2011-06-06 19 2012-06-06 How do I group by a time period either i.e. minutes, hours, days, or years etc.? Thanks!
sql-server-2008sql-server-2008-r2tsql
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Keep in mind that you can't use anything more granular than days when using DATEPART on a DATE datatype (hours and minutes are out)
1 Like 1 ·
Forgive me, but what to the numbers 10,17,19 represent? I'm not sure that I understand how those correlate to the date. Is that a count of the number of rows?
0 Likes 0 ·
Never mind. Just realized that it's the sum of the request values.
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
Something like this what you are after? SELECT SUM(request), creation FROM #request GROUP BY creation or SELECT SUM(request), DATEPART(YEAR,creation) FROM #request GROUP BY DATEPART(YEAR, creation)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Beat me by 5 seconds! ;-)
0 Likes 0 ·
yea it was the group by datepart bit i was looking for thanks!
0 Likes 0 ·
JohnM avatar image
JohnM answered
This should do the trick: SELECT SUM(request), creation FROM #request GROUP BY creation GO Hope this helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.