| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL Query Problem(possilble duplicate send, Sorry!)
Thanks for your reply! Will try it now. Will this work in 8i?
Viktor
Bricklen Anderson <BAnderson_at_presinet.com> 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)||'%'
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')
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bricklen Anderson INET: BAnderson_at_PresiNET.com 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: ListGuru_at_fatcity.com (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). --------------------------------- Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes --0-2038679230-1074031088=:47688 Content-Type: text/html; charset=us-ascii <DIV>Thanks for your reply! Will try it now. Will this work in 8i?</DIV> <DIV> </DIV> <DIV>Viktor</DIV> <DIV> </DIV> <DIV><BR><B><I>Bricklen Anderson <BAnderson_at_presinet.com></I></B> wrote:</DIV> <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Viktor wrote:<BR><BR>> Hello all,<BR>> <BR>> I am working with a query that does some counts. I've hit a brick wall <BR>> and can't get passed trying to figure out how I can make the query show <BR>> percentages %.<BR>> <BR>> Here is the query:<BR>> <BR>> select "COUNTRY",<BR>> count ("MSS") "COUNT_MSS"<BR>> FROM (<BR>> SELECT DISTINCT<BR>> ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,<BR>> m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ <BR>> "NAME_ID",<BR>> NVL(a.country,'USA') "COUNTRY"<BR>> FROM mscript ms, reviewms m, address a, journal j<BR>> WHERE (m.first_return between '01/01/2003' and '12/31/2003'<BR>> or m.second_return between '01/01/2003 and '12/31/2003'<BR>> or m.second_return between '01/01/2003 and '12/31/2003')<BR>> and ms.journal_id = j.journal_id<BR>> ! and M.MSNUMBER_JCO! DE = ms.journal_id<BR>> and M.MSNUMBER_YRISSUE = ms.yr_of_issue<BR>> and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no<BR>> and M.MSNUMBER_CKCHAR = ms.check_char<BR>> and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)<BR>> and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)<BR>> and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)<BR>> and a.addtype_addrstyp in ('m', 'p')<BR>> and a.addtype_typeno = 1<BR>> and (a.addr_end_date is null<BR>> or a.addr_end_date > sysdate)<BR>> and (first_recommend not in <BR>> ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')<BR>> or second_recommend not in <BR>> ('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))<BR>> order by 1)<BR>> GROUP BY ROLLUP(COUNTRY)<BR>> <BR>> And the output:<BR>> <BR>> COUNTRY COUNT_MSS<BR>> <BR>> AUSTRALIA 1<BR>> AUSTRIA 2<BR>> BELGIUM 4<BR>> CANADA 20<BR>> CHILE 1<BR>> CHINA 3<BR>> CZECH REPUBLIC 1<BR>> ! DENMARK 4<BR>> ENGLAND 10<BR>> 46<BR>> <BR>> Is there a way I can also display a percentage column, that is the <BR>> percentage of the total in the same SQL statement?<BR>> <BR>> As always thanks so much!<BR>> <BR>> Viktor<BR>> <BR><BR>Will this work? (ran a simple test case that worked, although this may not):<BR><BR>select <BR>country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%' <BR>pct<BR>from (<BR>select "COUNTRY",<BR>count ("MSS") over (partition by "MSS") "COUNT_MSS",<BR>count("MSS") over () overall_total<BR>FROM (<BR>SELECT DISTINCT<BR>ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,<BR>m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ <BR>"NAME_ID",<BR>NVL(a.country,'USA') "COUNTRY"<BR>FROM mscript ms, reviewms m, address a, journal j<BR>WHERE (m.first_return between '01/01/2003' and '12/31/2003'<BR>or m.second_return between '01/01/2003 and '12/31/2003'<BR>or m.second_return! between '01/01/2003 and '12/31/2003')<BR>and ms.journal_id = j.journal_id<BR>and M.MSNUMBER_JCO! DE = ms.journal_id<BR>and M.MSNUMBER_YRISSUE = ms.yr_of_issue<BR>and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no<BR>and M.MSNUMBER_CKCHAR = ms.check_char<BR>and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)<BR>and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)<BR>and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)<BR>and a.addtype_addrstyp in ('m', 'p')<BR>and a.addtype_typeno = 1<BR>and (a.addr_end_date is null<BR>or a.addr_end_date > sysdate)<BR>and (first_recommend not in <BR>('1','2','3','4','5','6','7','8','9','F','P','N','S','u')<BR>or second_recommend not in <BR>('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))<BR>order by 1)<BR>GROUP BY ROLLUP(COUNTRY))<BR><BR>(not sure how it will react to a 'group by rollup..' though.<BR><BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.net<BR>-- <BR>Author: Bricklen Anderson<BR>INET: BAnderson@PresiNET.com<BR><BR>Fat C! ity Network Services -- 858-538-5051 http://www.fatcity.com<BR>San Diego, California -- Mailing list and web hosting services<BR>---------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><hr SIZE=1> Do you Yahoo!?<br> Yahoo! Hotjobs: <a href="http://pa.yahoo.com/*http://us.rd.yahoo.com/hotjobs/mail_footer_email/evt=21482/*http://hotjobs.sweepstakes.yahoo.com/signingbonus">Enter the "Signing Bonus" Sweepstakes</a> --0-2038679230-1074031088=:47688-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Viktor INET: stant_98_at_yahoo.com 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: ListGuru_at_fatcity.com (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).Received on Tue Jan 13 2004 - 15:59:25 CST
 |
 |