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

my table is--

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

and values --

insert mobile values(10,'nokia',100); insert mobile values(11,'samsung',150); insert mobile values(12,'sony',120); 

i know how calculate average on column cost, code is--

 select avg(cost) mobile; 

and result 123

but want calculate average , show difference.i able but, not able add avg column in select query--

my code ---

select id, m_name "mobile name", cost price,avg(cost) average, cost-(select avg(cost) mobile) difference mobile group id,m_name,cost; 

and output --

id      mobile name   price  average  difference  10      nokia         100    100      -23 11      samsung       150    150       27 12      sony          120    120      -3 

what wants correct average column.. 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...

your group aggregates average, , grouping whole table (i assuming did allow select everything) move avg subquery, remove overarching group , should solve it.

select id, m_name "mobile name", cost price,     (select avg(cost) mobile) average,     cost-(select avg(cost) mobile) difference  mobile; 

when run basic select avg(cost) statement naturally grouping column specified (cost in case) requesting. suggest reading more on group by , aggregates better grasp on concept. should more simple solution.

update:

the answer below david's answer. makes use analytical functions. basically, happening on each avg call, telling engine use function (in case, nothing). decent writeup on analytical functions can found here , here , more google on matter.

select id, m_name "mobile name" cost price, avg(cost) over( ) average,      cost - avg(cost) on ( ) difference     mobile 

however, if sql engine allows variables, below answer. prefer future maintainability/readability. reason variable name can descriptive future readers of code, versus analytical function require little bit more work read (especially if not understand on function).

also, solution duplicates same query twice, might worth storing average in sql variable. ca change statement use global average

this variables in sql-server (you have adapt own instance of sql)

declare @my_avg int; select @my_avg = avg(cost) mobile;      select id, m_name "mobile name", cost price,         @my_avg average, cost-@my_avg difference      mobile; 

this solution read lot cleaner future readers of sql, too