RE: Interesting Challenge
mysql SELECT cell, sector, If you only want one row, then selecting cell doesn't make any sense. Cell is different in each row you've selected. If you only want one row, don't select cell. - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell Group by cell means Give me a total for each cell. If you only want one row, you don't need a group by at all; if you want one row per sector, you should group by sector. - HAVING sector=1 AND (cell=148 or cell=3); This doesn't belong in the Having clause. This needlessly slows you query down. This can go in the where clause. See http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SELECT. - 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: Interesting Challenge
This simply returns me to the documentation. Thanks -Original Message- From: James Northcott [mailto:jnorthcott;dpmg.com] Sent: Monday, November 04, 2002 12:13 PM To: Mysql (E-mail) Subject: RE: Interesting Challenge mysql SELECT cell, sector, If you only want one row, then selecting cell doesn't make any sense. Cell is different in each row you've selected. If you only want one row, don't select cell. - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell Group by cell means Give me a total for each cell. If you only want one row, you don't need a group by at all; if you want one row per sector, you should group by sector. - HAVING sector=1 AND (cell=148 or cell=3); This doesn't belong in the Having clause. This needlessly slows you query down. This can go in the where clause. See http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SEL ECT. - 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 - 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: Interesting Challenge
I know this is not elegant, but have have you tried using a temporary table? It adds up your function column correctly. There was an example of this earlier today from Oyekanmi - Re: getting around a subselect, http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:123911:200211:onbajmklkgifeckohcpa Try create temporary table temp your query here; select sum(cell),sum(sector),sum(att), sum(etc) from temp; John Black, Kelly W [PCS] wrote: Hi sql query wizards! I need some help. Is there a way I can take this query here = mysql SELECT cell, sector, - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell - HAVING sector=1 AND (cell=148 or cell=3); +--++--+--+--+---+--+--+--+--+-- --+--+ | cell | sector | att | lc | csh | drops | tccf | bpp | bpc | suf | blocks | mou | +--++--+--+--+---+--+--+--+--+-- --+--+ |3 | 1 | 734 | 12 |6 | 2.52 | 21 |0 |0 |0 | 2.86 | 1501 | | 148 | 1 | 2746 | 93 | 30 | 4.59 | 63 |0 |0 |1 | 2.33 | 4672 | +--++--+--+--+---+--+--+--+--+-- --+--+ And have it display the two rows as a total sum together in one row? I have been struggling with this and could really use some help. Thanks in advance for any ideas. Regards, Kelly Black Linux was very clearly the answer, but what was the question again? - 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 - 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: Interesting Challenge
Thanks!!! I appreciate all the help I can get. I am trying to validate what appears to be a working query, and will post back to the list as soon as I can confirm it works. I think many others will benefit from my working this out.. Thanks again and all my best! ~Kelly W. Black -Original Message- From: James Northcott [mailto:jnorthcott;dpmg.com] Sent: Monday, November 04, 2002 2:29 PM To: Black, Kelly W [PCS] Subject: RE: Interesting Challenge I have tried with and without having. Neither works. If you try running the query without cell, or sector, the result is an sql query error. I would remove both cell and sector from select, and move the having stuff into the where clause. Why don't you try just: select sum(att) as att from ss where release=CURDATE() and sector=1 AND (cell=148 or cell=3) I will try that link...thanks for the input. ~Kelly W. Black -Original Message- From: James Northcott [mailto:jnorthcott;dpmg.com] Sent: Monday, November 04, 2002 12:13 PM To: Mysql (E-mail) Subject: RE: Interesting Challenge mysql SELECT cell, sector, If you only want one row, then selecting cell doesn't make any sense. Cell is different in each row you've selected. If you only want one row, don't select cell. - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell Group by cell means Give me a total for each cell. If you only want one row, you don't need a group by at all; if you want one row per sector, you should group by sector. - HAVING sector=1 AND (cell=148 or cell=3); This doesn't belong in the Having clause. This needlessly slows you query down. This can go in the where clause. See http://www.mysql.com/documentation/mysql/bychapter/manual_Refe rence.html#SEL ECT. - 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 - 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
