--0-874124217-1074025973=:51707
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:
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
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
--0-874124217-1074025973=:51707
Content-Type: text/html; charset=us-ascii
<DIV>Hello all,<BR> <BR>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 %.<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 "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 ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')<BR> or second_recommend not in
('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 </DIV>
<DIV> 46<BR> <BR>Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement?<BR> <BR>As always thanks so much!<BR> <BR>Viktor<BR> <BR></DIV><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-874124217-1074025973=:51707--
--
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 - 14:34:35 CST