Solved

# Summing an Average Value in Oracle

Posted on 2004-04-06

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:

SELECT TEMP_2.SUM_AVG_VAL, TEMP.AVG_VAL

FROM

(

SELECT SUM(AVG_VAL) AS SUM_AVG_VAL,

product_heading

FROM

( SELECT AVG(VAL) AS AVG_VAL,

product_heading,

timeband

FROM .....

GROUP BY

product_heading,

timeband

) 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.