My best code is this: SELECT SUM(invoice_amount), state, statement_date FROM invoice GROUP BY state, statement_date
The results are have followed: +---------------------+---------------+----------------+ | 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 | +---------------------+---------------+----------------+ 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 | +---------------------+---------------+----------------+ Stephane -----Original Message----- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 02, 2002 3:47 PM To: Danis Stéphane (NHQ-AC); [EMAIL PROTECTED] Subject: Re: SQL Query Help Stéphane, > I have INVOICE table here is the layout: > > +-------------------------+---------------+------+-----+---------+------ ---- > ------+ > | Field | Type | Null | Key | Default | Extra > | > +-------------------------+---------------+------+-----+---------+------ ---- > ------+ > | 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 | | > | ... | | | | | > +------------+---------+---------+---------+---------+ > > Any idea, I tried a bunch of different syntax without any solutions. > mysql, query This can be done in a single query... How many different states do you want to list in columns? Would it be easier to list the months as columns/switch rows and cols? It doesn't much matter but are we talking significant numbers of rows? What is your best code so far/the problem(s) that need fixing? =dn --------------------------------------------------------------------- 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
