Mike,
I have a large table of filenames and creation dates from which I want to produce a histogram.
SELECT year(date), quarter(date), count(0) FROM pics WHERE date(date) > '2000' AND date(date) < ' 2005' GROUP BY year(date), quarter(date)
Gets me close, but to simplify plotting, I want to include rows for the dates where no files were created.
One way requiring no app code is to create a calendar table (in your case with cols for year & quarter, and a row for each quarter that is to be summed), and left join the calendar table on year & quarter to your pics table.
PB ----- Mike Martin wrote:
I have a large table of filenames and creation dates from which I want to produce a histogram. SELECT year(date), quarter(date), count(0) FROM pics WHERE date(date) > '2000' AND date(date) < ' 2005' GROUP BY year(date), quarter(date) Gets me close, but to simplify plotting, I want to include rows for the dates where no files were created. I get this: +------------+---------------+----------+ | year(date) | quarter(date) | count(0) | +------------+---------------+----------+ | 2001 | 3 | 34 | | 2002 | 1 | 2 | | 2002 | 4 | 1 | | 2003 | 2 | 1 | | 2003 | 3 | 1 | | 2003 | 4 | 3 | | 2004 | 1 | 1 | | 2004 | 2 | 1 | | 2004 | 3 | 5 | | 2004 | 4 | 1 | +------------+---------------+----------+ I want this: +------------+---------------+----------+ | year(date) | quarter(date) | count(0) | +------------+---------------+----------+ | 2001 | 1 | 0 | | 2001 | 2 | 0 | | 2001 | 3 | 34 | | 2001 | 4 | 0 | | 2002 | 1 | 2 | | 2002 | 2 | 0 | | 2002 | 3 | 0 | | 2002 | 4 | 1 | | 2003 | 1 | 0 | | 2003 | 2 | 1 | | 2003 | 3 | 1 | | 2003 | 4 | 3 | | 2004 | 1 | 1 | | 2004 | 2 | 1 | | 2004 | 3 | 5 | | 2004 | 4 | 1 | +------------+---------------+----------+ Thanks in advance for your help! MikeMartin
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.17/228 - Release Date: 1/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
