I would typically do this in code, but you could also create a stored procedure that does something like:
- create a temporary table - populate it with all possible values with a count field set to 0 - run an REPLACE INTO temp_table <your query goes here> - dump the contents of the temp table You may also want to just have an empty copy of the temp table with all the 0 values if you need to run this query frequently -- it may be easy to copy the data instead of creating it each run.. On 1/13/06, Mike Martin <[EMAIL PROTECTED]> 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 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
