CODEWITHSUNDEEP
sql
Patrick Rey
Patrick Rey

Reputation: 129

Get the AVG of an AVG result

I have this query :

SELECT AVG(legs.avg) FROM legs INNER JOIN matchs ON matchs.id = legs.match_id WHERE matchs.player_id=4 GROUP BY match_id

Which allows me to get the average of the attribute "legs.avg".

The problem is that I get several results for this query, one for each matchs.id.

I need to get the average of these different results, so only one row with the total average.

Is that possible ?

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

One approach is to get the "average of averages":

SELECT avg(l_avg)
FROM (SELECT AVG(l.avg) as l_avg
      FROM legs l INNER JOIN
           matchs m
           ON m.id = l.match_id
      WHERE m.player_id = 4
      GROUP BY l.match_id 
     ) lm;

There are two other approaches with no subquery:

SELECT AVG(l.avg) as l_avg
FROM legs l INNER JOIN
     matchs m
     ON m.id = l.match_id
WHERE m.player_id = 4;

Or:

SELECT SUM(l.avg) / COUNT(DISTINCT l.match_id) as l_avg
FROM legs l INNER JOIN
     matchs m
     ON m.id = l.match_id
WHERE m.player_id = 4;

These do not return the same value. The first is the overall average and the second is weighted so each match has a weight of exactly 1. This is the same as the first first query, with the subquery.

Without sample data, it is not clear which version you really want.

Upvotes: 1

Gaurav
Gaurav

Reputation: 1109

Use below query :- 

select avg(leg_avg) from (SELECT AVG(legs.avg) leg_avg FROM legs INNER JOIN matchs ON matchs.id = legs.match_id WHERE matchs.player_id=4 GROUP BY match_id) a11

Upvotes: 0

Related Questions