We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Summing an Average Value in Oracle

Medium Priority
Last Modified: 2012-06-27
I have a query where I need to find the average value of a product by a time band and a product category (for a cross-tab report)

                   PROD1         PROD2   PROD3   PROD4

Overnight     Avg for        Avg for        
                   PROD1 &     PROD2 &
                   Overnight    Overnight etc.

1 DAY         Avg for              
                   PROD1 &   
                  1 DAY               .......

2 DAY         .........

TOTAL        SUM of          SUM of
                  avg values      avg values
                  for all             for all timebands
                  timebands for  for
                  PROD1            PROD2

I also need to SUM these average values by product heading as shown in the
above table (TOTAL section).

Therefore I need the SUM by product heading of the average
values and the AVG by product heading and timeband of the values.
The grouping has to be this way - I cannot calculate the SUM and AVG by product heading and timeband and sort out the total in Crystal Reports as I change the numbers to characters in the DB (after doing all the calculations on them of course). This is so that I can place a 'B' or 'M' for billions and millions on the end of the number as the values are large. I really want to be able
to do this in one SQL statement (not PL/SQL) avoiding the need for a view.

I have a piece of SQL as follows:

       FROM .....
       GROUP BY
     )  TEMP
GROUP BY product_heading
) TEMP_2
WHERE TEMP.product_heading = TEMP_2.product_heading

This would give me what I want but unfortunately Oracle won't let me reference AVG_VAL from the inner inline view. Obviously I can get around this by using a separate view to calculate AVG_VAL but I don't want to do this if at all possible.

Using SUM(AVG(val)) and grouping by product_heading doesn't give the desired results either.

For clarity the number to character conversions and other where clause restrictions have not been shown.
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)


Yup that would be great - unfortunately I'm using Oracle 8i!!!

What tool are you writing this crosstab report with?

In SQL Plus you could use a standard "pivot" query:

select timeband,
 avg( decode( product_heading, 'PROD1', val, null ) ) prod1_avg,
 avg( decode( product_heading, 'PROD2', val, null ) ) prod2_avg,
 avg( decode( product_heading, 'PROD3', val, null ) ) prod3_avg,
from temp
group by timeband;

along with the SQL Plus commands:

compute sum of prod1_avg on report
compute sum of prod2_avg on report
compute sum of prod3_avg on report
break on report


Crystal Reports



Thanks for the responses - I had a working report in Crystal using a method similar to your above proposition but with one snag.

105,000,000 becomes 105M , 150,000,000 becomes 150M in my report. You can't sum 105M + 150M as they are chars. So my method worked but only if I didn't do this conversion and used the raw numbers. Unfortunately the numbers are so large that they need to be shortened in this fashion and there's no way I can see of doing it in Crystal - hence I've done it (or tried to)in Oracle. This report would be easy apart from this problem!! This is why I need to do all the averaging and summing before converting to chars.

Unlock this solution and get a sample of our free trial.
(No credit card required)


Rajnadimpalli and Andrewst,

Thanks for your answers guys. Andrew's first answer would have worked for Oracle 9i (my fault for not specifying db version) and Rajnadimpalli's analytic functions would have worked for 8i (I say would have as the spec for the report has changed and I can now implement the solution in a much more straightforward manner). Therefore I will split the points 125 to Rajnadimpalli because it's a direct solution for my db version and 75 to Andrewst.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.