I'm using Hylafax and sending the stats to a PostgreSQL table called
faxreports. I'm needing to report on error types per hour and don't
know how to compose the SQL query. I'm thinking I may need a union,
intersect, group by, or having clause in a certain way, but don't know
what's the best approach.

To make this simple, let's imagine the columns are only uid (unique
ID), faxdate (date + 24 hour time), and errmsg (which is either '' or
a statement of an error type (of which there are about 15 types)).

The end result will show me a report like:

Hour | No Dial Tone | No Answer | Aborted | Timeout | Success
-------------------------------------------------------------------------------
0 | 0 | 1 | 40 | 3 | 56
1 | 4 | 3 | 0 | 0 | 67
....
23 | 2 | 0 | 0 | 0 | 244

Note on a given hour, several faxes may come in, some timeout, some
abort, etc.

Another way to write it could be to build it like:

Hour | Error Type | Occurrences
0 | No Dial Tone | 0
0 | No Answer | 1
....etc.

Does anyone know how to convert the input table into one of the two
kinds of output tables?

On 24.04.2007 01:02, Alfred wrote:
For the second solution you just need GROUP BY if I understand your
input correctly. The first one is a tad more complex, you'll need
several CASE expressions.

select hour
, sum(case error when 'foo' then 1 else 0) foo_errors
, sum(case error when 'bar' then 1 else 0) bar_errors
....
group by hour
order by hour

robert