From: "Danis Stéphane (NHQ-AC)" <[EMAIL PROTECTED]> > My best code is this: > > SELECT SUM(invoice_amount), state, statement_date > FROM invoice > GROUP BY state, statement_date > > > The results are have followed:
<snip> > 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: > > +---------------------+---------------+----------------+ > | 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 | > +---------------------+---------------+----------------+ As far as as I can tell, the following query should deliver what you want: SELECT SUM(invoice_amount), state, statement_date FROM invoice ORDER BY statement_date, state Let me know if this does the job! -- denonymous www.coldcircuit.net --------------------------------------------------------------------- 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
