Stéphane, Answers: There are nine different states (and there are 12 months). ***** The main problem is the layout and the order of the results set. I will have 9 state (provinces) and I would prefer the month to be the columns. The ideal results would look like this, Please note I have only included 5 state and there is 9 but you see what I'm looking for, also the statement_date is always the 1st of the month: ***** It would therefore not make sense to switch rows and cols! [It doesn't much matter but are we talking significant numbers of rows?] - I assume we are, so ...
I said "This can be done in a single query" - let's get on with it:- > INVOICE table layout: > +-------------------------+---------------+------+-----+---------+------ ---- > ------+ > | ID | int(11) | | PRI | NULL | > auto_increment | > | CLIENT_NAME | int(11) | YES | | NULL | > | > | STATE | varchar(255) | YES | | NULL | > | > | STATEMENT_DATE | date | YES | | NULL | > | > | INVOICE_AMOUNT | double(16,2) | YES | | NULL | > | > | LAST_MODIFIED_DATE | timestamp(14) | YES | | NULL | > | > | ACTIVE_FLAG | tinyint(1) | YES | | NULL | > | > +-------------------------+---------------+------+-----+---------+------ ---- > ------+ > > I would like to produce a result set that would give me the following > report: > it would be a sum of the INVOICE_AMOUNT grouped by state(STATE) and > month(STATEMENT_DATE). > > +------------+---------+---------+---------+---------+ > | MONTH | STATE_1 | STATE_2 | STATE_3 | ... | > +------------+---------+---------+---------+---------+ > | JANUARY | 1234 | 12345 | 124 | | > | FEBRUARY | 2536 | 65874 | 457 | | > | MARCH | 4578 | 87452 | 547 | | > | ... | | | | | > +------------+---------+---------+---------+---------+ SELECT SUM(invoice_amount), state, statement_date FROM invoice GROUP BY state, statement_date +---------------------+---------------+----------------+ | SUM(invoice_amount) | state | statement_date | +---------------------+---------------+----------------+ | 65389.35 | Manitoba | 2001-12-01 | | 194224.45 | New Brunswick | 2001-12-01 | | 271516.40 | Quebec | 2001-12-01 | | 361673.95 | Quebec | 2002-01-01 | +---------------------+---------------+----------------+ +---------------------+---------------+----------------+ | SUM(invoice_amount) | state | statement_date | +---------------------+---------------+----------------+ | 0.00 | Alberta | 2001-12-01 | | 65389.35 | Manitoba | 2001-12-01 | | 194224.45 | New Brunswick | 2001-12-01 | | 0.00 | Ontario | 2001-12-01 | | 271516.40 | Quebec | 2001-12-01 | | 0.00 | Alberta | 2002-01-01 | | 0.00 | Manitoba | 2002-01-01 | | 0.00 | New Brunswick | 2002-01-01 | | 0.00 | Ontario | 2002-01-01 | | 361673.95 | Quebec | 2002-01-01 | +---------------------+---------------+----------------+ Let's deal with the months first (ref man: 6.3.4 Date and Time Functions). Put the month name in the left-hand column by extracting it from the statement date column: SELECT MONTH( statement_date ), as Month, SUM(invoice_amount), state FROM invoice GROUP BY state, statement_date should produce something like: | Month | SUM(invoice_amount) | state | | December | 0.00 | Alberta | | December | 65389.35 | Manitoba | | December | 194224.45 | New Brunswick | | December | 0.00 | Ontario | | December | 271516.40 | Quebec | | January | 0.00 | Alberta | | January | 0.00 | Manitoba | | January | 0.00 | New Brunswick | | January | 0.00 | Ontario | | January | 361673.95 | Quebec | and while we're dealing with temporal matters, don't forget that if you have more than twelve month's worth of data March/April may appear more than once in the output! The next step is to get those states into columns instead of rows. Let's start with Alberta (where else?) - and only Alberta - so what we'd like to see is something like: SELECT MONTH( statement_date ), as Month, SUM(invoice_amount) FROM invoice WHERE state = "Alberta" GROUP BY statement_date | Month | SUM(invoice_amount) | | December | 0.00 | | January | 0.00 | but that won't scale up because when we work with more than one state, the WHERE clause is going to end up mixing state/data/lists again. Is there another way to achieve the same thing? (ref man: 6.3.1.4 Control Flow Functions) Try: SELECT MONTH( statement_date ), as Month, SUM( IF( state = "Alberta", invoice_amount, 0 ) ) AS Alberta FROM invoice GROUP BY statement_date | Month | Alberta | | December | 0.00 | | January | 0.00 | Perhaps we should have chosen Quebec where they speak French, SQL, and English (and in that order of preference)!? Once you've satisfied yourself that you have that working, replicate the SUM(IF()) across the columns/states. If you want a country-wide total for the month, copy the existing SUM() in as the last column (with an AS alias to put a better label on the output report). Which leaves us only with the question: is Alberta a state or a province, eh? Regards, =dn PS warning: I have not checked any of this code on my machine. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
