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.