[EMAIL PROTECTED] wrote on 01/13/2006 10:16:44 AM: > 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 >
If you need all of your quarters listed (even if they don't exist in your data) then you need to create a table that lists all of them then JOIN to it in order to get all of the year-quarter combinations. You could set it up something like this: CREATE TABLE chartbase_YrQtr (yr int, qtr int, KEY(yr, qtr))ENGINE=MYISAM; create temporary table tmpYr SELECT distinct YEAR(`date(date)`) yr from pics; create temporary table tmpQtr (qtr int); INSERT tmpQtr VALUES (1),(2),(3),(4); INSERT chartbase_YrQtr(yr, qtr) SELECT tmpYr.yr, tmpQtr.qtr FROM tmpYr, tmpQtr; /* note: I am intentionally declaring a Cartesian product in this query in order to quickly generate all of the possible combinations of years and quarters. This is the only situation where I use the implicit CROSS JOIN syntax for its readability. */ DROP TEMPORARY TABLE tmpYr, tmpQtr; That would turn your original query into SELECT cb.yr year, cb.qtr quarter, count(pics.`quarter(date)`) FROM chartbase_YrQtr cb LEFT JOIN pics ON year(pics.`date(date)`) = cb.Yr AND pics.`quarter(date)` = cb.qtr WHERE cb.yr > '2000' AND cb.yr < ' 2005' GROUP cb.yr, cb.qtr; Basically, it boils down to the fact that it's not easy to get the database to return to you data it never has in the first place. By providing it with a master table of years and quarters, you give it something to use in order to "detect" which values aren't present. Databases are very good at calculations but they don't have much "real-world" knowledge. That's what the extra table in combination with your original query provides: it informs the database that for each year, quarter(date) should take on each of the values from 1 to 4. How else would the engine "know" that fact? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
