Hi,
On Wed, Jul 29, 2009 at 4:56 PM, Aaron Fulton <aaronful...@softhome.net>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?
>
Make a calendar table - in your case just year-month and left join your
report query. You could also make a per day calendar table and group it
first then join.
HTH
Kind Regards,
Jochen Daum
Chief Automation Officer
Automatem Ltd
Phone: 09 630 3425
Mobile: 021 567 853
Email: j...@automatem.co.nz
Skype: jochendaum
Website: www.automatem.co.nz
http://twitter.com/automatem
http://www.xing.com/go/invite/3425509.181107
>
> Aaron
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to nzphpug@googlegroups.com
To unsubscribe, send email to
nzphpug+unsubscr...@googlegroups.com
-~----------~----~----~----~------~----~------~--~---
