RE: SQL Query Problem(possilble duplicate send, Sorry!)

From: Rohan Karanjawala <rohanjava_at_hotmail.com>
Date: Tue, 13 Jan 2004 20:14:26 -0800
Message-ID: <F001.005DCAB4.20040113201426@fatcity.com>

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 <stant_98_at_yahoo.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: rohanjava_at_hotmail.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 - 22:14:26 CST