How to calculate average of a column and then include it in a select query in oracle?

Question

My table is--

create table mobile
(
  id integer,
  m_name  varchar(20),
  cost integer
)

and the values are --

insert into mobile values(10,'NOkia',100);
insert into mobile values(11,'Samsung',150);
insert into mobile values(12,'Sony',120);

I know how to calculate average on column cost, my code is--

 select avg(cost) from mobile;

and the result is 123

But i want to calculate average and then also show the difference.I was able to this but, I am not able to add avg column in the select query--

My code is ---

SELECT id, m_name as "Mobile Name", cost as Price,avg(cost) as Average,
cost-(select avg(cost) from mobile) as Difference FROM mobile
group by id,m_name,cost;

and the output is --

id      Mobile Name   Price  Average  Difference 
10      Nokia         100    100      -23
11      Samsung       150    150       27
12      Sony          120    120      -3

what I wants is to correct this average column.. I wants this---

id      Mobile Name  Price  Average  Difference 
10      Nokia       100     123     -23
11      Samsung     150     123      27
12      Sony        120     123      -3

please help...

+1 for supplying a `create table` and sample data as `insert`, but that is not valid Oracle syntax (Oracle does not have `bigint` or `int ). And numbers should not be put in single quotes! — , Mar 10 '12 at 15:55
@JustinPihony, you can use `[faq]`, [faq], `[ask]`, [ask] etc instead, see http://stackoverflow.com/editing-help#comment-formatting — Ben, Mar 10 '12 at 16:31
@Ben Thanks, I have been meaning to look comment formatting up. Much appreciated. — Justin Pihony, Mar 10 '12 at 16:45

score 11 · Answer 1 · answered Mar 10 '12 at 16:04

Since you're using Oracle, you should be able to use AVG() as an analytic (window) function:

SELECT id, m_name AS "Mobile Name" cost AS Price, AVG(cost) OVER( ) AS Average
     , cost - AVG(cost) OVER ( ) AS Difference
  FROM mobile

No need for subqueries or GROUP BY.

Justin Pihony · Accepted Answer · 2012-03-10T17:06:03.427

Your group by is what aggregates your average, and it is grouping by the whole table (I am assuming you did this to allow the select for everything) Just move your avg into another subquery, remove the overarching group by and that should solve it.

SELECT id, m_name AS "Mobile Name", cost AS Price,
    (SELECT AVG(cost) FROM mobile) AS Average,
    cost-(SELECT AVG(cost) FROM mobile) AS Difference 
FROM mobile;

When you run the basic SELECT AVG(cost) statement it is naturally grouping by the column specified (cost in this case) as that is what you are requesting. I would suggest reading up more on GROUP BY and aggregates to get a better grasp on the concept. That should help you more than just a simple solution.

UPDATE:

The answer below is actually from David's answer. It makes use the analytical functions. Basically, what is happening is that on each AVG call, you are telling the engine what to use for the function (in this case, nothing). A decent writeup on analytical functions can be found here and here and more with a google on the matter.

SELECT id, m_name AS "Mobile Name" cost AS Price, AVG(cost) OVER( ) AS Average, 
    cost - AVG(cost) OVER ( ) AS Difference
    FROM mobile

However, if your SQL engine allows for variables, you could just as easily do the below answer. I actually prefer this for future maintainability/readability. The reason is that a variable with a good name can be very descriptive to future readers of the code, versus an analytical function that does require a little bit more work to read (especially if you do not understand the over function).

Also, this solution duplicates the same query twice, so it might be worth storing your average in a SQL variable. Then you ca change your statement to simply use that global average

This is variables in SQL-Server (you will have to adapt it for your own instance of SQL)

DECLARE @my_avg INT;
SELECT @my_avg = AVG(cost) FROM Mobile;

    SELECT id, m_name AS "Mobile Name", cost AS Price,
        @my_avg AS Average, cost-@my_avg AS Difference 
    FROM mobile;

This solution will read a lot cleaner to future readers of your SQL, too

I'm pretty sure David's query is at least as efficient and does not require the use of a PL/SQL block which requires you to put the result of the SELECT into variables - it will not be displayed the way you wrote it (actually I think it wouldn't even run). And besides the duplicated query will only be run once by Oracle. I'm pretty sure the optimizer is smart enough to detect that. — , Mar 10 '12 at 16:21
I appreciate your explanation, but the PL/SQL block is simply wrong. That approach will never work in Oracle. If you don't want to repeat the avg() query, use the approach with the analytical function or the cross join solution. — , Mar 10 '12 at 16:29
That doesn't make the PL/SQL correct. It is still *wrong* and **will not run** — , Mar 10 '12 at 16:44
@Justin, I think it is up to the person doing the answering to correct his answer. It is not up to others to do the correcting for him. — David Faber, Mar 10 '12 at 17:36
My final answer should hopefully encompass everything, and have explanations that will allow the user to learn the why, and not just get the answer without explanation. — Justin Pihony, Mar 10 '12 at 17:40

score 2 · Answer 3 · answered Mar 10 '12 at 15:43

The simplest change is to change avg(cost) as Average to (select avg(cost) from mobile) as Average. This also means that you won't need the GROUP BY clause anymore (since it doesn't do what you actually wanted):

SELECT id,
       m_name AS "Mobile Name",
       cost AS "Price",
       (SELECT AVG(cost) FROM mobile) AS "Average",
       cost - (SELECT AVG(cost) FROM mobile) AS "Difference"
  FROM mobile
;

score 0 · Answer 4 · edited Aug 08 '16 at 20:01

0

select pid, name, price as actualcost, 
       AVERAGE = (select AVG(price) from Part_Master), 
       price - (select AVG(price) as diff from Part_Master) AS COST_DIFF 
from   Part_Master

edited Aug 08 '16 at 20:01

answered Aug 08 '16 at 17:44

RAJU

1

score 0 · Answer 5 · answered Mar 10 '12 at 15:42

try

SELECT id, m_name as "Mobile Name", cost as Price,(select avg(cost) from mobile) as Average),
cost-(select avg(cost) from mobile) as Difference FROM mobile
group by id,m_name,cost;

if your query is too expensive that way drop me a commend then I'll improve it.

score 0 · Answer 6 · answered Mar 10 '12 at 15:59

One of the rare times a CROSS JOIN is applicable:

WITH avgcost as (select round(avg(cost)) as Average from mobile)
SELECT id, m_name as "Mobile Name", cost as Price, Average,
cost-Average as Difference
FROM mobile cross join avgcost

Which will result in:

ID  Mobile Name PRICE   AVERAGE DIFFERENCE
10  NOkia       100     123     -23
11  Samsung     150     123     27
12  Sony        120     123     -3

How to calculate average of a column and then include it in a select query in oracle?

6 Answers6

Linked