Search Postgresql Archives

Re: GROUP BY hour

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Nathan Thatcher escreveu:
I have, what I imagine to be, a fairly simple question. I have a query
that produces output for a line graph. Each row represents an interval
on the graph.

SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
FROM c_call WHERE  start_time >= '2008-08-01 00:00:00' AND end_time <=
'2008-08-01 23:59:59' GROUP BY hour


This works great when there is data in each interval but when a given
interval has no data the group is omitted. What is the best way to
ensure that the result contains a row for each interval with the value
field set to zero or null? The reporting tool is incapable of filling
in the gaps.




Try:
SELECT s.hour::int, coalesce(t.value,0) FROM generate_series(0,23) AS s(hour)
LEFT OUTER JOIN
(SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
FROM c_call WHERE date_trunc('day',start_time) = '2008-08-01' GROUP BY hour) AS t
ON s.hour = t.hour;

Osvaldo



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux