RE: SQL Query Help
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
RE: SQL Query Help
I didn't have time to try out your hints/techniques described in your earlier post, but after playing around a bit with the web frontend I decided that it would be more user intuitive to used the month as column and the state as row... The reason being my change of heart is I also have a similar report to build for cities, so I prefer using the variable data (ie. state/city) as rows and keep the fixed data (ie. month) as column. Stephane -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 11:01 AM To: Danis Stéphane (NHQ-AC); [EMAIL PROTECTED] Subject: Re: SQL Query Help Stéphane, Have just re-read my response... * 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: * After asking you if the months and states/rows and columns could be transposed, was I dozy enough to suggest an answer that is the wrong way around??? Whilst I would recommend that the answer table be constructed to have fewer columns than rows, maybe you have your reasons... If you do want to transpose the answer given, can you manage it from the hints/techniques described, or do you need me to take another run at it? Regards, =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
SQL Query Help
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. Stephane mysql, query - 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
MySQL Book
Any book you guys recommend for learning the specific strenght of MySQL, I'm have a Oracle background and would like to learn more on MySQL. Stephane SQL, Query - 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
Cannot UPDATE mysql record in ASP
I get this error when trying to update a record using ASP recordsets. Microsoft OLE DB Provider for ODBC Drivers error '80040e21' Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. /Web_local/admin/xt_save.asp, line 39 What is really strange is that the line # is referencing a line where I assign a value to a field see code below the line is marked with asterisk. Sub Update() Set rs = Server.CreateObject(ADODB.Recordset) sSql = select * from table where ID=ID rs.Open sSql,aCn,3,3 *--rs(DESCRIPTION) = strDesc rs(DETAILS) = strDetails rs(LAST_MODIFIED_DATE)= Now() rs(ACTIVE_FLAG) = 1 rs.Update rs.Close Set rs = Nothing End Sub I tried a bunch of different things and I always get his error, any clue on what is going on! BTW, this is my first system using MySQL as a back end. Stephane - 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
