Re: SQL Query Problem(possilble duplicate send, Sorry!)
Viktor wrote: Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %. Here is the query: select COUNTRY, count (MSS) COUNT_MSS FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ NAME_ID, NVL(a.country,'USA') COUNTRY FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id and M.MSNUMBER_JCO! DE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY) And the output: COUNTRYCOUNT_MSS AUSTRALIA 1 AUSTRIA 2 BELGIUM 4 CANADA 20 CHILE 1 CHINA 3 CZECH REPUBLIC1 DENMARK 4 ENGLAND 10 46 Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement? As always thanks so much! Viktor Will this work? (ran a simple test case that worked, although this may not): select country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%' pct from ( select COUNTRY, count (MSS) over (partition by MSS) COUNT_MSS, count(MSS) over () overall_total FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ NAME_ID, NVL(a.country,'USA') COUNTRY FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id and M.MSNUMBER_JCO! DE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY)) (not sure how it will react to a 'group by rollup..' though. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bricklen Anderson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Query Problem(possilble duplicate send, Sorry!)
Thanks for your reply! Will try it now. Will this work in 8i? Viktor Bricklen Anderson [EMAIL PROTECTED] wrote: Viktor wrote: Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %. Here is the query: select "COUNTRY", count ("MSS") "COUNT_MSS" FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID", NVL(a.country,'USA') "COUNTRY" FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id ! and M.MSNUMBER_JCO! DE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY) And the output: COUNTRY COUNT_MSS AUSTRALIA 1 AUSTRIA 2 BELGIUM 4 CANADA 20 CHILE 1 CHINA 3 CZECH REPUBLIC 1 ! DENMARK 4 ENGLAND 10 46 Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement? As always thanks so much! Viktor Will this work? (ran a simple test case that worked, although this may not):select country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%' pctfrom (select "COUNTRY",count ("MSS") over (partition by "MSS") "COUNT_MSS",count("MSS") over () overall_totalFROM (SELECT DISTINCTms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID",NVL(a.country,'USA') "COUNTRY"FROM mscript ms, reviewms m, address a, journal jWHERE (m.first_return between '01/01/2003' and '12/31/2003'or m.second_return between '01/01/2003 and '12/31/2003'or m.second_return! between '01/01/2003 and '12/31/2003')and ms.journal_id = j.journal_idand M.MSNUMBER_JCO! DE = ms.journal_idand M.MSNUMBER_YRISSUE = ms.yr_of_issueand M.MSNUMBER_MS_SEQNO = ms.ms_sequence_noand M.MSNUMBER_CKCHAR = ms.check_charand m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)and a.addtype_addrstyp in ('m', 'p')and a.addtype_typeno = 1and (a.addr_end_date is nullor a.addr_end_date sysdate)and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))order by 1)GROUP BY ROLLUP(COUNTRY))(not sure how it will react to a 'group by rollup..' though.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bricklen AndersonINET: [EMAIL PROTECTED]Fat C! ity Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
Re: SQL Query Problem(possilble duplicate send, Sorry!)
Viktor wrote: Thanks for your reply! Will try it now. Will this work in 8i? Viktor If it works at all, then it should work in both 8i and 9i, although I don't have a version of 8i handy right now to try this on. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bricklen Anderson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Query Problem(possilble duplicate send, Sorry!)
hi, There is a function RATIO_BY_PERCENT or something very similar to this in SQL just find it out this gives u individual contributions as compared to the whole thing. Regds, Rohan From: Viktor [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: SQL Query Problem(possilble duplicate send, Sorry!) Date: Tue, 13 Jan 2004 12:34:35 -0800 Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %. Here is the query: select COUNTRY, count (MSS) COUNT_MSS FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ NAME_ID, NVL(a.country,'USA') COUNTRY FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id and M.MSNUMBER_JCODE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY) And the output: COUNTRYCOUNT_MSS AUSTRALIA 1 AUSTRIA 2 BELGIUM 4 CANADA 20 CHILE 1 CHINA 3 CZECH REPUBLIC1 DENMARK 4 ENGLAND 10 46 Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement? As always thanks so much! Viktor - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes _ Contact brides grooms FREE! http://www.shaadi.com/ptnr.php?ptnr=hmltag Only on www.shaadi.com. Register now! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rohan Karanjawala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).