CODEWITHSUNDEEP
mysql
Kamlesh Kumar
Kamlesh Kumar

Reputation: 1680

How to get average value according their value in mysql

I'm trying to get result of data and their hole average, according to their value, example : 

select sum(rank) as value,area from temp GROUP BY area

value | Area
------+-------
15    | East
54    | North
49    | South
35    | West

but I want to their total avg like 

value | Area  | Average
------+-------+---------
49    | East  | 40
15    | North | 40
56    | South | 40
40    | West  | 40

How I can get these ? plz help

Upvotes: 0

Views: 25

Answers (2)

Matt Cooper
Matt Cooper

Reputation: 937

select sum(rank) as value, area, avg(value) from temp GROUP BY area

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can use a subquery. Does this give you the results you want?

select x.value, t.area
from temp t cross join
     (select sum(rank) / count(distinct area) as value from temp) x
group by t.area, x.value;

Upvotes: 1

Related Questions