List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 13 2006 3:47pm
Subject:Re: Histogram from tables.
View as plain text  
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

Thread
Histogram from tables.Mike Martin13 Jan
  • Re: Histogram from tables.Gary Richardson13 Jan
  • Re: Histogram from tables.Peter Brawley13 Jan
  • Re: Histogram from tables.Pooly13 Jan
  • Re: Histogram from tables.Wolfram Kraus13 Jan
  • Re: Histogram from tables.SGreen13 Jan