Hi:
since your use postgres, check this function generate_series(),
i think your can generate a series date, then left join result.
regards,
anru
On Jul 29, 2009, at 4:56 PM, Aaron Fulton wrote:
>
> Hi,
>
> I'm working on some reports which show the number of page views over a
> period of time. I use google analytics for most pages, but there
> are a
> few specific pages that require more specific reporting.
>
> I am currently doing all the grouping and counting of the data via sql
> queries (postgres) eg.
> SELECT to_char(timestamp, 'YYYY Mon') AS name, COUNT(*) AS value
> FROM {statistics_view}
> GROUP BY to_char(timestamp, 'YYYY Mon')
> ORDER BY 1 ASC
>
> My problem is that the query only returns rows where there is data
> (as you
> would expect).
>
> for example if there were 10 page views in Jan and 10 in Mar, but
> none in
> Feb, Feb would be missing from the query results.
>
> however for reporting I would like to show a zero value for the
> periods
> where there are no page views (ie show Feb).
>
> Does anyone know of any sql tricks to get group names for periods
> where
> there are no page views? Or do I need to head to php land to fill
> in the
> blank periods?
>
> Aaron
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
-~----------~----~----~----~------~----~------~--~---
