SQL Query Problem(possilble duplicate send, Sorry!)

From: Viktor <stant_98_at_yahoo.com>
Date: Tue, 13 Jan 2004 12:34:35 -0800
Message-ID: <F001.005DCA8C.20040113123435@fatcity.com>

--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>&nbsp;<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>&nbsp;<BR>Here is the query:<BR>&nbsp;<BR>select "COUNTRY",<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; count ("MSS") "COUNT_MSS"<BR>FROM (<BR>&nbsp;SELECT DISTINCT<BR>&nbsp;ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,<BR>&nbsp;m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID",<BR>&nbsp;NVL(a.country,'USA') "COUNTRY"<BR>&nbsp;FROM mscript ms, reviewms m, address a, journal j<BR>&nbsp;WHERE (m.first_return between '01/01/2003' and '12/31/2003'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; or m.second_return between '01/01/2003 and '12/31/2003'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; or m.second_return between '01/01/2003 and '12/31/2003')<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and ms.journal_id = j.journal_id<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and M.MSNUMBER_JCO! DE&nbsp;

• ms.journal_id<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and M.MSNUMBER_YRISSUE = ms.yr_of_issue<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and M.MSNUMBER_CKCHAR = ms.check_char<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and M.RVIEWNUM_INIT_SITE&nbsp; = a.namekey_init_site(+)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and a.addtype_addrstyp in ('m', 'p')<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and a.addtype_typeno = 1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and (a.addr_end_date is null<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; or a.addr_end_date &gt; sysdate)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))<BR>&nbsp;order by 1)<BR>GROUP BY ROLLUP(COUNTRY)<BR>&nbsp;<BR>And the output:<BR>&nbsp;<BR>COUNTRY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT_MSS<BR>&nbsp;<BR>AUSTRALIA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;1 <BR>AUSTRIA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 <BR>BELGIUM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 <BR>CANADA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20 <BR>CHILE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 <BR>CHINA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 3 <BR>CZECH REPUBLIC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 <BR>DENMARK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 <BR>ENGLAND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 </DIV> <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 46<BR>&nbsp;<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>&nbsp;<BR>As always thanks so much!<BR>&nbsp;<BR>Viktor<BR>&nbsp;<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