The easiest way is make a 'Tally' table and then do some date add function to generate a date range, (depends on sql flavour) or more elegantly (but somewhat less efficient) is to have a function return a tally table (again depends on type of SQL)
A numeric tally table (rather than a date range table) can be used for a number of things (also fun working out the population routine) HTH Neven > Hi, > > > > On Wed, Jul 29, 2009 at 4:56 PM, Aaron Fulton > <[email protected] <mailto:[email protected]>> 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: [email protected] <mailto:[email protected]> > Skype: jochendaum > Website: www.automatem.co.nz <http://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 [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
