Summing an Average Value in Oracle

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.
RichardKnightAsked:
Who is Participating?
 
rajnadimpalliCommented:
How about addressing this using 8i Analytic Functions....Can you try...If possible post your complete query..to get it right..using Analytic Functions...

select
sum(a.AVG_VAL) over (partition by product_heading) as "SUM_AVG_VAL",        
a.AVG_VAL as "AVG_VAL"
from
(SELECT AVG(VAL) AS AVG_VAL,product_heading,timeband
 FROM .....
 ........
 GROUP BY
 product_heading,
 timeband
) A


-Raj
0
 
andrewstCommented:
In 9i you can do it like this:

WITH temp AS
     ( SELECT AVG(VAL) AS AVG_VAL,
       product_heading,
       timeband
       FROM .....
       GROUP BY
       product_heading,
       timeband
     )
SELECT TEMP_2.SUM_AVG_VAL, TEMP.AVG_VAL
FROM temp,
(
SELECT SUM(AVG_VAL) AS SUM_AVG_VAL,
            product_heading
FROM temp
GROUP BY product_heading
) TEMP_2
WHERE TEMP.product_heading = TEMP_2.product_heading;


Hope I have that right - haven't got 9i available right now.
0
 
RichardKnightAuthor Commented:
Yup that would be great - unfortunately I'm using Oracle 8i!!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
andrewstCommented:
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

0
 
RichardKnightAuthor Commented:
Crystal Reports
0
 
RichardKnightAuthor Commented:
Andrewst,

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.


0
 
RichardKnightAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.