Re: sql for retrieving statistic

mago22_at_hotmail.com (maria granholm) wrote

> I have a database collecting alarms. now I want to get statistic from
> the last month. and I want the average alarms to be presented per hour
> and after severity.

<snipped>

To display data for an hour for which you do not have data.. I suggest a fixed table called HOUR, that is populated with 24 rows - one for each hour.

You can then outer join your stats table to this hour table and output data for hours where there are no alarms.

You can also expand this table to a TIME table that contains a row for every second of the day. Same principle applies, but allows you a much finer grain than a HOUR table.

> and is it also possible to display a Sum after each hour?

Look at the GROUP BY and ROLLUP clauses in the SQL Reference Manual. There are examples given too (in your case you will do something like a "group by rollup(hour)" as the aggregation clause for your query).

--
Billy