Solved

SQL: Average, Subtract, and sub queries

Posted on 2014-04-22
12
1,074 Views
Last Modified: 2014-04-22
INVOICE ( INV_NUM, CUST_NUM, INV_AMOUNT)   (the existing columns that are used)

I'm working on learning SQL from Oracle's website and a book I bought. I want to build a query that  will show INV_NUM, INV_AMOUNT.

Along with that I want it to show two more columns derived from the AVG of the INV_AMOUNT column. That will be called INV_AVG

I also want the difference shown between INV_AVG and INV_AMOUNT, that will be called INV_DIFF

Below are the individual statements I created...but now I have to combine it all together into one statement.


SELECT INV_NUM, INV_AMOUNT
FROM INVOICE;


SELECT AVG(INV_AMOUNT) as INV_AVG
FROM INVOICE;

SELECT INV_AVG - INV_AMOUNT
FROM INVOICE;

Open in new window



I know I have to be way off, but I was thinking something along the lines of:

SELECT INV_NUM, INV_AMOUNT, INV_AVG, INV_DIFF
WHERE INV_DIFF = INV_AVG - INV_AMOUNT AND INV_AMOUNT = AVG(INV_AMOUNT)
FROM INVOICE;

Open in new window


Any ideas where to start?
0
Comment
Question by:Pancake_Effect
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 67 total points
Comment Utility
select a.inv_num, a.inv_amount, b.inv_avg, (b.inv_avg - a.inv_amount) inv_diff from
(select inv_num, inv_amount
 from invoice a,
 (select inv_num, avg(inv_amount) inv_avg
  from invoice
  group by inv_num) b
where a.inv_num = b.inv_num);
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 299 total points
Comment Utility
See if this gets you started:

SELECT INV_NUM, INV_AMOUNT, AVG(INV_AMOUNT) over() TOTAL_AVG
FROM INVOICE;

Then you can wrap a select outside it to do whatever you want:

select inv_num, inv_amount, total_avg, ...
from
(
SELECT INV_NUM, INV_AMOUNT, AVG(INV_AMOUNT) over() TOTAL_AVG
FROM INVOICE
);


Now if you want the AVG per Invoice:
SELECT INV_NUM, INV_AMOUNT, AVG(INV_AMOUNT) over() TOTAL_AVG,
AVG(INV_AMOUNT) over(partition by cust_num)  CUST_INV_AVG
FROM INVOICE
0
 
LVL 5

Assisted Solution

by:Jalpa Kotak
Jalpa Kotak earned 134 total points
Comment Utility
SELECT INV_NUM, INV_AMOUNT, (SELECT AVG(INV_AMOUNT) FROM INVOICE) AS INV_AVG,AMOUNT-(SELECT AVG(AMOUNT) FROM INVOICE) AS INV_DIFF
FROM INVOICE;
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 299 total points
Comment Utility
Should add:  The window functions are very powerful once you start to understand them.

I would start with the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#i1007779
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 299 total points
Comment Utility
jalpa_144's method may work but makes some unnecessary table accesses for each of the inline columns.

For the inline view without the Window functions, follow awking00's approach.

The problems with that style is it accesses the table more than once.  If you can use the Window functions, you only hit the table once.
0
 
LVL 4

Author Comment

by:Pancake_Effect
Comment Utility
just using jalpa_144's post as an example how would you limit it to 2 decimals? Normally it's not hard, simply by using the round function..but there is a lot of brackets.

SELECT 
INV_NUM, 
INV_AMOUNT, 
(SELECT AVG(INV_AMOUNT) FROM INVOICE) AS INV_AVG,
INV_AMOUNT-(SELECT AVG(INV_AMOUNT) FROM INVOICE) AS INV_DIFF
FROM INVOICE;

Open in new window

Would it be something similar to:

SELECT 
INV_NUM, 
INV_AMOUNT, 
(SELECT ROUND (AVG(INV_AMOUNT) FROM INVOICE),2) AS INV_AVG,
INV_AMOUNT-(SELECT  ROUND (AVG(INV_AMOUNT) FROM INVOICE),2) AS INV_DIFF
FROM INVOICE;

Open in new window


Doesn't seem to like my method though :(
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.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 299 total points
Comment Utility
Again, I would encourage you to not use that method...

That said:Make the ROUND the outside call:

round((...everything else that goes in here that you want rounded...),2)
0
 
LVL 5

Accepted Solution

by:
Jalpa Kotak earned 134 total points
Comment Utility
SELECT
INV_NUM,
INV_AMOUNT,
(SELECT ROUND (AVG(INV_AMOUNT),2) FROM INVOICE) AS INV_AVG,
INV_AMOUNT-(SELECT  ROUND (AVG(INV_AMOUNT),2) FROM INVOICE) AS INV_DIFF
FROM INVOICE;
0
 
LVL 4

Author Comment

by:Pancake_Effect
Comment Utility
@jalpa_144 Ah okay, I was doing it too far down. Thanks!

@slightwv Thanks as well. I'm reading over the link about windowing now, I think it will be helpful. It's a little confusing but I think I will just have to practice at it.
0
 
LVL 4

Author Closing Comment

by:Pancake_Effect
Comment Utility
Thanks everyone, lot's of answers. That's the one I understand for right now, because I'm really basic, but I will definitely look into your other approaches, I'm open to any workflows that will make me more efficient. It'll be good to pick up these tips as I continue on learning SQL. Thanks again everyone.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I wish you hadn't closed this that quick.  I was working on a test case to show why you don't want to do it that way.

>>That's the one I understand for right now

But why learn the WRONG way to do something?  It will set in some really bad habits.

Take a look at the simple test case below.  They both return the exact same results.
Mine hits the table once.

jalpa_144's method hits the table three times.

Which should you be using?

Now say you want breakdowns by total average and customer average?

Mine is a simple tweak.  The other, more full table scans.


Note: the autotrace calls are for SQL plus to show actual execution plan and statistics.
drop table tab1 purge;
create table tab1(INV_NUM number, CUST_NUM number, INV_AMOUNT number);

insert into tab1 values(1,1,100);
insert into tab1 values(2,1,100);
insert into tab1 values(3,2,100);
insert into tab1 values(4,2,200);
insert into tab1 values(5,2,300);
commit;

set autotrace on

select inv_num, inv_amount, total_avg, inv_amount-total_avg inv_diff
from
(
SELECT INV_NUM,
	INV_AMOUNT,
	AVG(INV_AMOUNT) OVER(partition by cust_num) CUST_AVG,
	AVG(INV_AMOUNT) OVER() TOTAL_AVG
FROM tab1
);


SELECT 
INV_NUM, 
INV_AMOUNT, 
(SELECT ROUND (AVG(INV_AMOUNT),2) FROM tab1) AS INV_AVG,
INV_AMOUNT-(SELECT  ROUND (AVG(INV_AMOUNT),2) FROM tab1) AS INV_DIFF
FROM tab1; 

set autotrace off

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
hear, hear!

using aggregate functions such as AVG() you appear to know, so the next small step is to add OVER()

compare these

a: select AVG(INV_AMOUNT) from tab1;

b: select AVG(INV_AMOUNT) OVER() from tab1;

a:  1 full table scan, results in a single number
b: 1 full table scan, results in the same number as a: but repeated for each row of tab1

so by simply adding " OVER() " I get a value needed on each row!
&, Once it is on each row I can "do the math" just by including extra columns but I am not adding any extra table scans.
using slightwv's sample data

    create table tab1(INV_NUM number, CUST_NUM number, INV_AMOUNT number);
    
    insert into tab1 values(1,1,100);
    insert into tab1 values(2,1,100);
    insert into tab1 values(3,2,100);
    insert into tab1 values(4,2,200);
    insert into tab1 values(5,2,300);

    --a: 
    select AVG(INV_AMOUNT) from tab1

   
    | AVG(INV_AMOUNT) |
    |-----------------|
    |             160 |


    --b: 
    select AVG(INV_AMOUNT) OVER() from tab1
    
    --a: results in a single number
    --b: results in the same number as a: but repeated for each row of tab1
    

    | AVG(INV_AMOUNT)OVER() |
    |-----------------------|
    |                   160 |
    |                   160 |
    |                   160 |
    |                   160 |
    |                   160 |



  [1]: http://sqlfiddle.com/#!4/4e9175/4

Open in new window

then by simple extension of query b: we arrive at query d:
    --c:
    -- include the extra columns
    
    SELECT
            INV_NUM
          , INV_AMOUNT
          , AVG(INV_AMOUNT) OVER() TOTAL_AVG
    FROM tab1
    
    | INV_NUM | INV_AMOUNT | TOTAL_AVG |
    |---------|------------|-----------|
    |       1 |        100 |       160 |
    |       2 |        100 |       160 |
    |       3 |        100 |       160 |
    |       4 |        200 |       160 |
    |       5 |        300 |       160 |


    -- d:
    -- now "nest" c: to perform the row by row arithmetic
    SELECT
            inv_num
          , inv_amount
          , total_avg
          , inv_amount-total_avg inv_diff
    FROM (
          --c:
          SELECT
                INV_NUM
              , INV_AMOUNT
              , AVG(INV_AMOUNT) OVER() TOTAL_AVG
          FROM tab1
          )
    	
    | INV_NUM | INV_AMOUNT | TOTAL_AVG | INV_DIFF |
    |---------|------------|-----------|----------|
    |       1 |        100 |       160 |      -60 |
    |       2 |        100 |       160 |      -60 |
    |       3 |        100 |       160 |      -60 |
    |       4 |        200 |       160 |       40 |
    |       5 |        300 |       160 |      140 |

  [1]: http://sqlfiddle.com/#!4/4e9175/5

Open in new window

and this still only requires 1 table scan! This is a substantial improvement over a solution that requires 3 table scans.

in short, using " OVER() " is all you need to learn here
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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

743 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

17 Experts available now in Live!

Get 1:1 Help Now