Aggregate SQL column values by time period
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:开发者_如何学Python26: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.
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'
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.
I do not see how you could use DATEPART
for this since it cannot return only the date part of a DATETIME
value (correct me if I am mistaken). What does work is use DATEADD
to "null" the time and group by a value of the form YYYY-MM-DD 00:00:00.000
.
The following query works against the Adventure Works database in case you happen to have it (tested on SQL Server 2005). Other than using DATEADD
it is very similar to @OMG Ponies suggestion:
select
dateadd(dd, 0, datediff(dd, 0, OrderDate)) as SaleDay
, sum(taxamt) as Amount
from
Sales.SalesOrderHeader
group by
dateadd(dd, 0, datediff(dd, 0, OrderDate))
order by
SaleDay
The idea of dateadd(dd, 0, datediff(dd, 0, OrderDate))
is to first get the "number of days passed from the beginning of time until your date" (the datediff
-part) and then add this number of days to "the beginning of time". Which gives you the "start of your day". I hope this is understandable :)
精彩评论