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

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