How to calculate the average value of a column and then include it in the 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 the average value for a column cost, my code is

  select avg(cost) from mobile; 

and result 123

But I want to calculate the average and then show the difference. I was able to do this, but I cannot add the 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 way out is

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

I want to adjust this middle column. I want this ---

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

please, help...

Answer 1

Your group is what aggregates your average, and it is grouped throughout the table (I assume you did this to allow a choice for everything). Just move your avg to another subquery, delete the whole group, 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) , it is naturally grouped by the specified column (cost in this case), because this is what you request. I would advise reading more GROUP BY and aggregates until a better understanding of the concept. This should help you not just as a simple solution.

UPDATE:

The answer below is actually from David's answer. It uses analytic functions. Basically, what happens is that on every AVG call you tell the engine what to use for the function (in this case, nothing). A nice record of analytic functions can be found here and here and more with google on this.

 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 variables, you can just as easily follow the answer below. I really prefer this for future maintainability / readability. The reason is that a variable with a good name can be very descriptive for future code readers, compared to an analytic function that requires a bit more work to read (especially if you don't understand the function).

In addition, this solution duplicates the same query twice, so you should save the average value in an SQL variable. Then you can change your statement to just use this global average

These are variables in SQL Server (you will have to adapt it for your own SQL instance)

 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 much cleaner for future readers of your SQL, as well

Answer 2

Since you are using Oracle, you should use AVG () as an analytic function (window):

 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.

Answer 3

The simplest change is to change avg(cost) as Average to (select avg(cost) from mobile) as Average . This also means that you will no longer need the GROUP BY (since it does not do what you really need):

 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 ; 

Answer 4

to 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 request is too expensive to drop my grade, I will improve it.

Answer 5

One of the rare times a CROSS JOIN 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 

This will lead to:

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

Answer 6

 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