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:
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
_________________________________________________________________
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).
