Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Aggregate SQL column values by time period

Tags:

sql

sql-server

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.

like image 601
Sundance Avatar asked Mar 31 '10 05:03

Sundance


2 Answers

U can use CAST to date type

SELECT [ENTRY_DATE],CAST([ENTRY_DATE] AS date) AS 'date' 
FROM [PROFIT_LIST]

Now you can group by according to this.

SELECT CAST([ENTRY_DATE] AS date) AS 'date',SUM(PROFIT) 
FROM [PROFIT_LIST]
GROUP BY CAST([ENTRY_DATE] AS date) AS 'date'
like image 120
Imrul Avatar answered Sep 23 '22 01:09

Imrul


Here's a nice trick. If you cast a SQL DATETIME to a FLOAT it gives you the date as days.fractionofday

Therefore if you floor that, and turn it back to a DATETIME it gives you minight on the given date.

CAST(FLOOR(CAST(MyDateTime  AS FLOAT)) AS DATETIME)

Therefore, my favourite way of doing this is.

select
    CAST(FLOOR(CAST(OrderDate AS FLOAT)) AS DATETIME)
    , sum(taxamt) as Amount
from
    Sales.SalesOrderHeader
group by
        CAST(FLOOR(CAST(OrderDate AS FLOAT)) AS DATETIME)

I have no idea if that is more/less eficient than any previous correct answers.

like image 21
Jamiec Avatar answered Sep 22 '22 01:09

Jamiec