Re: Oracle Query Math

On Nov 29, 5:51 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 29, 4:30 pm, trp..._at_gmail.com wrote:
>
>
>
>
>
> > I have a query:
>
> > SELECT GROUP_NAME, PERCENT FROM GROUPSUMMARY
> > WHERE REPORT_DATE= TO_DATE( '2007-10-01','YYYY-MM-DD')
> > ORDER BY PERCENT DESC;
>
> > I get results like this:
>
> > Group1 40
> > Group2 20
> > Group3 15
> > Group4 10
> > Group5 5
> > Group6 4
> > Group7 3
> > Group8 2
> > Group9 1
>
> > What I want is a query that returns the top 5 rows and then a sixth
> > row with a calculation that is the sum of any result rows past row 5,
> > which could be variable.
>
> > So I want the results to show this:
>
> > Group1 40
> > Group2 20
> > Group3 15
> > Group4 10
> > Group5 5
> > Other 10
>
> > Where "Other" is 100-(sum of rows 1-5).
>
> > Is this possible with a query? This is an Oracle 10G database, not
> > sure about the patch level. Thanks for your help!
>
> orafaq.com and elsewhere have examples of "top-5" queries, perhaps you
> can subquery everything that not exists in that and union all to it.
> Just thinking out loud, there may be better ways, may depend on
> whether they always add up to 100.
>
> jg
> --
> @home.com is bogus.
> As if anyone respects robots.txt once clicks are paid for.http://www.signonsandiego.com/news/tech/20071129-0542-searchengineacc...- Hide quoted text -
>
> - Show quoted text -

Not sure if it is the best way, but here is what I did:

SELECT GROUP_NAME,PERCENT FROM
(SELECT * FROM GROUPSUMMARY
WHERE REPORT_DATE= TO_DATE( '2007-10-01','YYYY-MM-DD') ORDER BY PERCENT DESC) WHERE ROWNUM <= 5

UNION SELECT A.txt, 100-SUM(B.PERCENT) FROM (SELECT 'OTHER' txt FROM DUAL) A,
(SELECT * FROM GROUPSUMMARY
WHERE REPORT_DATE= TO_DATE( '2007-10-01','YYYY-MM-DD') ORDER BY PERCENT DESC) B WHERE ROWNUM <= 5

Thanks for the help! Received on Thu Nov 29 2007 - 19:28:55 CST