If the problem is getting all those zeroes in between your actual data, one relatively easy way to do that is with an auxiliary table and an outer join. You could create a sequence table for the 24 hour values like this:
SQL
CREATE TABLE hours (hour SMALLINT NOT NULL PRIMARY KEY);
INSERT INTO hours VALUES (0), (1), (2), ..., (23);
You can then do an outer join on that table to get 1 row per hour and use COALESCE() to turn the NULLs into zeroes. However, you will also need to use some function to extract the hour portion from your time so that you can join on it. What that is will depend on your database product. The following works in MySQL.
SQL
SELECT hour, COALESCE(SUM(`in`), 0)
FROM Table1 RIGHT JOIN hours ON HOUR(Table1.time_end) = hours.hour
GROUP BY hour;
If it's important that you be able to access this as a constantly-updated table, as you describe, you can simply turn it into a view:
SQL
CREATE VIEW Table2 (hour, sum_day) AS SELECT hour, COALESCE(SUM(`in`), 0)
FROM Table1 RIGHT JOIN hours ON HOUR(Table1.time_end) = hours.hour
GROUP BY hour;
20 Aug, 2008 - 07:20 AM
| 