Menu
create table #Events ( EventID int identity primary key, StartDate datetime not null, EndDate datetime not null ) go insert into #Events (StartDate, EndDate) select '2007-01-01 12:44:12 AM', '2007-01-01 12:45:34 AM' union all select '2007-01-01 12:45:12 AM', '2007-01-01 12:46:34 AM' union all select '2007-01-01 12:46:12 AM', '2007-01-01 12:47:34 AM' union all select '2007-01-02 5:01:08 AM', '2007-01-02 5:05:37 AM' union all select '2007-01-02 5:50:08 AM', '2007-01-02 5:55:59 AM' union all select '2007-01-03 4:34:12 AM', '2007-01-03 4:55:18 AM' union all select '2007-01-07 3:12:23 AM', '2007-01-07 3:52:25 AM' (with apologies to http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server for harvesting their base sql)
I am trying to find the count of Events that occurred in an hour, so the result set would look like this:
2007-01-01 12:00 3 2007-01-02 5:00 2 2007-01-03 4:00 1 2007-01-07 3:00 1 I have been playing with dateadd and round and grouping but not getting it. Can anyone help?
Thanks.
616
Here is the SQL query to get data for every hour in MySQL. In the above query, we simply group by order_date using HOUR function and aggregate amount column using SUM function. HOUR function retrieves hour number from a given date/time/datetime value, which can be provided as a literal string or column name.
1 Answer. You can use DATE_FORMAT operator. If you are using this you can easily group the date, timestamp or datetime column using whatever format you want.
SQL – count() with Group By clause The count() function is an aggregate function use to find the count of the rows that satisfy the fixed conditions. The count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
How about this? Assuming SQL Server 2008:
SELECT CAST(StartDate as date) AS ForDate, DATEPART(hour,StartDate) AS OnHour, COUNT(*) AS Totals FROM #Events GROUP BY CAST(StartDate as date), DATEPART(hour,StartDate) For pre-2008:
SELECT DATEADD(day,datediff(day,0,StartDate),0) AS ForDate, DATEPART(hour,StartDate) AS OnHour, COUNT(*) AS Totals FROM #Events GROUP BY CAST(StartDate as date), DATEPART(hour,StartDate) This results in :
ForDate | OnHour | Totals ----------------------------------------- 2011-08-09 00:00:00.000 12 3 Alternatively, just GROUP BY the hour and day:
SELECT CAST(Startdate as DATE) as 'StartDate', CAST(DATEPART(Hour, StartDate) as varchar) + ':00' as 'Hour', COUNT(*) as 'Ct' FROM #Events GROUP BY CAST(Startdate as DATE), DATEPART(Hour, StartDate) ORDER BY CAST(Startdate as DATE) ASC output:
StartDate Hour Ct 2007-01-01 0:00 3 2007-01-02 5:00 2 2007-01-03 4:00 1 2007-01-07 3:00 1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
