# Average of averages

I need to get the average of multiple averages calculated basing on data contained on a sql database. To do that, I'm using this code:

$a = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediaa  FROM values WHERE type ='a'"));
$b = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediab FROM values WHERE type ='b'"));
$c = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediac FROM values WHERE type ='c'"));
$d = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediad FROM values WHERE type ='d'"));
$e = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediae FROM values WHERE type ='e'"));
$f = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediaf FROM values WHERE type ='f'"));
$g = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediag FROM values WHERE type ='g'"));
$h = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediah FROM values WHERE type ='h'"));
$i = mysqli_fetch_assoc(mysqli_query($connessione,"SELECT AVG(voto) AS mediai FROM values WHERE type ='i'"));
$sum =$a['mediaa']+$b['mediab']+$c['mediac']+$d['mediad']+$e['mediae']+$f['mediaf']+$g['mediag']+$h['mediah']+$i['mediai'];
$globalavg = ($sum/9)*10;
echo 'Global average: ';
echo $globalavg;  But I believe this is not the best way to do this. How can I make my code cleaner? • @200_success I might be wrong, but [sum]/9 should be the average (and it can be a number from 0 to 10), multiplying it per 10 should return the value expressed in percentage (and that's what I need). – user46302 Sep 12 '14 at 15:53 • I don't know what units your original values are in, but multiplying by a factor of 10 is an unconventional way to convert something into a percentage. Sep 12 '14 at 16:12 • @200_success values in database can vary from 0 to 10. If they varied from 0 to 1, to get the percentage I'd multiply the average by 100. I know that this method pretty weird, but I really need the values to be stored like this :) – user46302 Sep 12 '14 at 16:19 ## 2 Answers Let the database do the hard work for you. Instead of selecting the values individually and summing them yourself, you can union your queries and sum the results in sql: SELECT SUM(x) / 9 * 10 AS globalavg FROM ( SELECT AVG(voto) AS x FROM values WHERE type ='a' UNION ALL SELECT AVG(voto) AS x FROM values WHERE type ='b' UNION ALL SELECT AVG(voto) AS x FROM values WHERE type ='c' UNION ALL -- follow the pattern for d, e, f, ... ) t  Assuming you put this query in a $query variable in PHP, your code can be simplified as:

$result = mysqli_fetch_assoc(mysqli_query($connessione, $query));$globalavg = $result['globalavg']; echo 'Global average: '; echo$globalavg;


Not only the PHP is simpler, it's more efficient to run one query than 9.

• Thanks :) I'm getting this error: "#1248 - Every derived table must have its own alias"
– user46302
Sep 11 '14 at 12:53
• Notice the last "t" at the end of the query: SELECT SUM(x) / 9 * 10 AS globalavg FROM (...) t, it's the alias for the inner query, and you get error #1248 if you forget to put an alias there Sep 11 '14 at 12:59
• Yeah, I had actually forgot it, now it's working. Thanks very much for your help :)
– user46302
Sep 11 '14 at 13:02

While I mostly agree with Janos, I think a better approach for the SQL query would be:

SELECT AVG(x) * 10 AS globalavg
FROM
(
SELECT AVG(voto) as x
FROM values
WHERE type in ('a','b','c','d','e','f','g','h','i')
GROUP BY type
) AS media


This avoids the UNION ALLs which should give you a bit of a performance boost.

• Thanks, after I have added an alias for the inner query it works :)
– user46302
Sep 12 '14 at 15:02
• I'm glad it works, I didn't actually test it before posting like I have previously. Sep 12 '14 at 15:05
• @200_sucess Thanks for the edit. I was going to do it myself but got distracted by something else. Sep 12 '14 at 15:51
• 9 is a magic number. You should write SUM(x) / 9 * 10 as 10 * AVG(x). Sep 12 '14 at 16:09
• @200_success Thanks again, I didn't notice that there were 9 types that the query was dealing with. Sep 12 '14 at 17:34