Re: Oracle Query Math

On Thu, 29 Nov 2007 17:28:55 -0800 (PST), trpost_at_gmail.com wrote:

>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!

It probably does not matter, but UNION ALL would skip the Sort Unique operation.

B. Received on Fri Nov 30 2007 - 08:31:49 CST