Aggregate SQL column values by time period
Posted
by user305688
on Stack Overflow
See other posts from Stack Overflow
or by user305688
Published on 2010-03-31T05:01:30Z
Indexed on
2010/03/31
5:03 UTC
Read the original article
Hit count: 158
sql
I have some numerical data that comes in every 5 minutes (i.e. 288 values per day, and quite a few days worth of data). I need to write a query that can return the sums of all values for each day. So currently the table looks like this:
03/30/2010 00:01:00 -- 553
03/30/2010 00:06:00 -- 558
03/30/2010 00:11:00 -- 565
03/30/2010 00:16:00 -- 565
03/30/2010 00:21:00 -- 558
03/30/2010 00:26:00 -- 566
03/30/2010 00:31:00 -- 553
...
And this goes on for 'x' number of days, I'd like the query to return 'x' number of rows, each of which containing the sum of all the values on each day. Something like this:
03/30/2010 -- <sum>
03/31/2010 -- <sum>
04/01/2010 -- <sum>
The query will go inside a Dundas webpart, so unfortunately I can't write custom user functions to assist it. All the logic needs to be in just the one big query. Any help would be appreciated, thanks. I'm trying to get it to work using GROUP BY and DATEPART at the moment, not sure if it's the right way to go about it.
© Stack Overflow or respective owner