Re: Oracle Query Math

On Nov 29, 4:30 pm, trp...@xxxxxxxxx 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-searchengineaccess.html
.

Relevant Pages

  • Re: Union without duplicates?
    ... Unless you have added the option ALL, the Union statement will automatically ... fields in a subquery and then add the other columns in a global query. ... I need to add Board Members into the mix. ... Plus I always end up with a column that is essentially bogus ...
    (microsoft.public.access.queries)
  • Re: ORDER BY Clause On Bit Value Failure using SELECT DISTINCT
    ... The last query is another alternative. ... descr varchar(25)) ... union all ... select distinct numval, descr, numval & 1 as bogus from #test ...
    (microsoft.public.sqlserver.programming)
  • Re: How do you assign an event to a query?
    ... It would be better not to open the query datasheet. ... not just open the second form, and then when you close it, there's the ... > the form, and it closes my form and opens the query, and then when I ... LouD ...
    (microsoft.public.access.queries)
  • RE: How do you assign an event to a query?
    ... Please disregard this question, I finally realized that I could just ... make a form based on the query and use a macro to do what I want. ... "LouD" wrote: ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)