SQL for hourly totals for the last 48 hours

However, I would like the summary to include every hour within the last 48, even if no records exist for that hour. In this case it would simply return a zero for that hour. Any ideas?

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle development languages How to check SQL query construction with the Mimer Validator Understanding SQL string functions The top advice from Oracle experts in 2008 What's the difference between an SQL inner join and equijoin? Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same table

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

We've seen this question--or ones very similar to it--before. However, it's been quite a while, so let's look at it again.

The solution relies on two techniques:

1. Generate the datetimes

   This is done by using a numbers table. The query will use a table with the numbers from 0 to 47 in it. Each number is used as an interval value in a date expression, subtracting from a base datetime value. This generates the 48 datetime values that mark the groups for the purposes of the join.

2. LEFT OUTER JOIN to your data

   The LEFT OUTER JOIN uses the 48 hourly datetime values as the "left" table, with your data as the "right" table, so that if no rows exist for a given hour, the aggregate functions will return zero because they ignore NULLs, and NULLs are what a LEFT OUTER JOIN produces for unmatched rows.

We start by taking the current datetime value and applying a "floor" or "truncate" technique to round it down to the nearest previous hour. In SQL, it can be done with an expression like this:

SELECT CURRENT_DATE + EXTRACT(HOUR FROM CURRENT_TIMESTAMP) HOUR

The EXTRACT function returns the current hour as an integer, e.g. 16 for 4 p.m. This number is then added, as an interval of hours, to the value of the current date, e.g. '2008-06-22'. The result is a timestamp for the beginning of the current hour, '2008-06-22 16:00:00'. Unfortunately, implementation varies from one DBMS to the next. In Oracle, you can instead simply TRUNC the SYSDATE value to the hour. In SQL Server, which has the GETDATE function for current timestamp but no equivalent CURRENT_DATE function, you can round down to the nearest previous hour like this:

DATEADD(HH,DATEDIFF(HH,'2000-01-01',GETDATE()),'2000-01-01')

Here '2000-01-01' is some arbitrary early "base date." The difference between this date and the current timestamp given by GETDATE(), as a number of whole hours, is added back to the base date, effectively rounding down to the nearest previous hour.

One satisfactory way to generate the 48 required numbers is to have an actual table of numbers:

SELECT n FROM numbers WHERE n BETWEEN 0 AND 47

Another way is to generate the numbers "on the fly" from a cross join of the integers table (the values 0 through 9) with itself:

SELECT 10 * t.i + u.i AS n FROM integers AS t CROSS JOIN integers AS u WHERE 10 * t.i + u.i BETWEEN 0 AND 47

Now we can use these 48 numbers as intervals to subtract from the starting hourly value, producing 48 hourly values. This is done in a subquery, which produces a derived table. The derived table becomes the left table in our LEFT OUTER JOIN to the data being summarized:

SELECT h.start_hour , SUM(mytable.amount) AS sum_amount FROM ( SELECT CURRENT_DATE + EXTRACT(HOUR FROM CURRENT_TIMESTAMP) HOUR - ( 10 * t.i + u.i ) HOUR AS start_hour FROM integers AS t CROSS JOIN integers AS u WHERE 10 * t.i + u.i BETWEEN 0 AND 47 ) AS h LEFT OUTER JOIN mytable ON mytable.datetimefield >= h.start_hour AND mytable.datetimefield < h.start_hour + 1 HOUR GROUP BY h.start_hour ORDER BY h.start_hour DESC