Thread: How to get results with zero count from this query?

Hello, I have a database table that contains information about the timestamp
and location(latitude and longtitude) of the requests made by users. The
column structure is as following:


requesttime(which is a timestamp without time zone type) | latitude |
longtitude


I have written the following code in order to retrieve the total requests
made day by day and hour interval by hour interval starting from a given
datetime to another given datetime:


SELECT date_trunc('hour', requesttime), COUNT(requesttime)
FROM mytable
WHERE requesttime between '2001-04-02 03:12:45' and '2006-02-05 23:14:00'
GROUP BY date_trunc('hour', requesttime)
ORDER BY date_trunc('hour', requesttime);

(a sample result from this query is 2003-07-11 21:00:00  | 121, meaning that
121 requests were made during the 21:00 - 22:00 hourly interval on july 11
2003)


however, I realized that this query skips printing out the time intervals
that have 0 requests -e.g. 2002-03-12 03:00:00 (the 03:00 - 04:00 am
interval) has 0 counts of requests but it directly skips printing that and
prints the 04:00-05:00 interval instead-


How can I make this query to also print out the rows with 0 counts in
addition to the original results? Thanks in advance.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-get-results-with-zero-count-from-this-query-tp5718595.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 08/01/12 8:14 PM, dud wrote:
> Hello, I have a database table that contains information about the timestamp
> and location(latitude and longtitude) of the requests made by users. The
> column structure is as following:
>
>
> requesttime(which is a timestamp without time zone type) | latitude |
> longtitude
>
>
> I have written the following code in order to retrieve the total requests
> made day by day and hour interval by hour interval starting from a given
> datetime to another given datetime:
>
>
> SELECT date_trunc('hour', requesttime), COUNT(requesttime)
> FROM mytable
> WHERE requesttime between '2001-04-02 03:12:45' and '2006-02-05 23:14:00'
> GROUP BY date_trunc('hour', requesttime)
> ORDER BY date_trunc('hour', requesttime);
>
> (a sample result from this query is 2003-07-11 21:00:00  | 121, meaning that
> 121 requests were made during the 21:00 - 22:00 hourly interval on july 11
> 2003)
>
>
> however, I realized that this query skips printing out the time intervals
> that have 0 requests -e.g. 2002-03-12 03:00:00 (the 03:00 - 04:00 am
> interval) has 0 counts of requests but it directly skips printing that and
> prints the 04:00-05:00 interval instead-
>
>
> How can I make this query to also print out the rows with 0 counts in
> addition to the original results? Thanks in advance.

You can probably do a left join with generate_series for the hour range
you want... the count() of the mssing values will probably be NULL, so
you may need to coalesce that to 0.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast