Thanks Anru, That's just what I'm after!
After a bit of googleing based on your suggestion, I found the exact thing I want to do. http://blog.redfin.com/devblog/2008/05/fun_with_generate_series.html Aaron > > 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] -~----------~----~----~----~------~----~------~--~---
