CODEWITHSUNDEEP
mysqlsql
AxOn
AxOn

Reputation: 101

How to calculate average value in mysql query?

I have

|  id  |  name  |  v1  |  v2 | rate_id  |
-------------------------------------
|  1      jack    202    2      18
|  2      jack    202    3      23
|  3      gad     204    2      34
|  4      vad     
|  5      mad
|  6      pad

avarage = (max rate + min rate) / 2 where name = jack AND v1 = 202 AND v2 = 2 also for each unique row for (name,v1,v2)

and result of avarage should be in other select query:

SELECT 
  m.rate = avarage
FROM messages m
INNER JOIN rates r
 ON r.id = m.rate_id

Upvotes: 0

Views: 68

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Use Group By and Aggregate 

select name,v1,v2, (max(rate) + min(rate)) / 2 As `Average`
From messages m
INNER JOIN rates r
 ON r.id = m.rate_id
Group by name,v1,v2

Upvotes: 1

Related Questions