Solved

Summing an Average Value in Oracle

Posted on 2004-04-06
7
1,128 Views
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:


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.
0
Comment
Question by:RichardKnight
  • 4
  • 2
7 Comments
 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 75 total points
ID: 10765174
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
 

Author Comment

by:RichardKnight
ID: 10765220
Yup that would be great - unfortunately I'm using Oracle 8i!!!
0
 
LVL 15

Expert Comment

by:andrewst
ID: 10765301
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:RichardKnight
ID: 10765411
Crystal Reports
0
 

Author Comment

by:RichardKnight
ID: 10766162
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
 
LVL 3

Accepted Solution

by:
rajnadimpalli earned 125 total points
ID: 10771211
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
 

Author Comment

by:RichardKnight
ID: 10782327
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Format Number Field 10 40
Oracle queries --Challenging Question 12 64
Oracle Database Upgrade 13 42
T-SQL Convert to PL/SQL 23 62
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now