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)||'%'
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 C! ity 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).
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
