Thread: GROUP BY hour
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. Thanks
Nathan Thatcher wrote: > 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. > > Thanks > > Use generate_series as part of your query. You can get a listing of all the hours, which can be integrated with your other data in a variety of ways, using: select '2008-08-01 00:00:00'::timestamp+generate_series(0,23)*'1 hour'::interval as hour; hour --------------------- 2008-08-01 00:00:00 2008-08-01 01:00:00 ... 2008-08-01 23:00:00
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
Brilliant! On Fri, Aug 1, 2008 at 12:18 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote: > Nathan Thatcher wrote: >> >> 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. >> >> Thanks >> >> > > Use generate_series as part of your query. You can get a listing of all the > hours, which can be integrated with your other data in a variety of ways, > using: > > select '2008-08-01 00:00:00'::timestamp+generate_series(0,23)*'1 > hour'::interval as hour; > > hour > --------------------- > 2008-08-01 00:00:00 > 2008-08-01 01:00:00 > ... > 2008-08-01 23:00:00 > >