I'm not *certain* of this but I'm pretty sure that SQL will never give you "zero rows" of the kind you want from a query on a single table. (Then again, I'm not an SQL Genius so maybe someone else can suggest something that would work.) However, you can probably accomplish what you want fairly easily by creating a second table. Populate the second table with every date in the date range you care about - such as every single date in 2005 - and then do an outer join between the second table and the first. That result should produce a row for every date, including the ones that aren't represented in the first table.
SQL would normally give you a null in the "Hits" column for rows whose date didn't exist in the first table but you should be able to use a coalesce() function to convert that to a zero. Mind you, null already means "unknown or not applicable" so maybe that is sufficient. The second table need not be permanent; you could generate it as a temp table when you need it so that it includes the appropriate range of dates (or days). In fact, that might be safer: if you make the date table permanent, you will have a problem later once your report is run for a period that falls fully or partially after the highest date in the date table. I assume you can write a program or script on your own to generate the INSERT statements that contain the desired dates. If you need code examples on any aspect of this, let me know and I can probably knock something together. Also, if you're not clear about the concept I'm suggesting, let me know and I'll try to clarify with a short example. Rhino ----- Original Message ----- From: "Erich Beyrent" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Tuesday, October 25, 2005 3:00 PM Subject: Reporting question > This is probably a silly question, but here goes... I have a table that > logs access to web services, and I am looking to create a report to > track the number of times each service was accessed in a given month. > > I know I can loop through the days in the month in PHP and do a seperate > query for each day, but is there a way to do this in MySQL and populate > dates not accessed with zeros? In other words, if I have hits for the > 23rd and 25th, but not the 24th, can I get something like > > +--------+------------+ > | Day | Hits | > +--------+------------+ > | 23 | 46 | > | 24 | 0 | > | 25 | 156 | > +--------+------------+ > > Thanks in advance! > > -- > Erich Beyrent > -------------------------- > http://www.beyrent.net > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.361 / Virus Database: 267.12.5/148 - Release Date: 25/10/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.361 / Virus Database: 267.12.5/148 - Release Date: 25/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
