Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1311
  • Last Modified:

SQL: Average, Subtract, and sub queries

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
Pancake_Effect
Asked:
Pancake_Effect
  • 5
  • 3
  • 2
  • +2
7 Solutions
 
awking00Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Jalpa KotakCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Pancake_EffectAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Jalpa KotakCommented:
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
 
Pancake_EffectAuthor Commented:
@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
 
Pancake_EffectAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
PortletPaulCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now