Solved

SQL: Average, Subtract, and sub queries

Posted on 2014-04-22
12
1,166 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 32

Assisted Solution

by:awking00
awking00 earned 67 total points
ID: 40015914
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 299 total points
ID: 40015918
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
ID: 40015924
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 299 total points
ID: 40015926
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 299 total points
ID: 40015932
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
ID: 40015983
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 299 total points
ID: 40015991
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
ID: 40016001
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
ID: 40016025
@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
ID: 40016029
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40016042
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
ID: 40016520
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 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