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
