Solved

SQL: Average, Subtract, and sub queries

Posted on 2014-04-22
12
1,194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 49

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

734 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