Re: Q getting a Percentage of a total.........?

% of Total is a tricky problem indeed. It was a supported feature of Reports 2.5, but it was calculated 'after the fact' - the data rows would be returned from the SQL query, then the reports engine would calculate the % of total for each value on the client side (i.e. in the reporting engine) once the total was a known quantity. It's a bit similar to printing 'Page m of n' on every page of a document when you don't know in advance how many pages there are going to be.

I can't say if later versions of Oracle SQL have evolved this capability yet - (i.e. including a total value within the intermediate calculations in a single query) although I suspect not. Can anyone confirm / deny?

Martin Doherty

Mike Cohen wrote:

>Hello Everyone,
>
>I've been banging my head with this problem and was hoping someone
>here could help out.
>
>I have the following query:
>
>select
>t2.DESC,
>round(((count(t1.PURPOSE)/18175)*100) ) Pct_Of_Total,
>round((avg(t1.PRICE)),0)
>from
>t_1 t1,
>t_2 t2
>...
>
>The problem I'm having is that I want where I have 18175 in my select
>to be a full count of t1.PURPOSE - sum of all the counts if you will.
>The number 18175 is the full count, yet I don't want it hardcoded in.
>I've tried:
>
>*sum(count(t1.purpose)) but this bombs on Group By errors...
>*searching OTN as well as various manuals...
>*searching the google archives...
>
>all with no real success.
>
>Does anyone have any ideas?
>
>Thanks in advance...
>
>Mike
>
>
Received on Fri Dec 06 2002 - 17:35:39 CST