5
\$\begingroup\$

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?

\$\endgroup\$
3
  • \$\begingroup\$ @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). \$\endgroup\$
    – user46302
    Sep 12 '14 at 15:53
  • \$\begingroup\$ 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. \$\endgroup\$ Sep 12 '14 at 16:12
  • \$\begingroup\$ @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 :) \$\endgroup\$
    – user46302
    Sep 12 '14 at 16:19
5
\$\begingroup\$

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.

\$\endgroup\$
3
  • \$\begingroup\$ Thanks :) I'm getting this error: "#1248 - Every derived table must have its own alias" \$\endgroup\$
    – user46302
    Sep 11 '14 at 12:53
  • \$\begingroup\$ 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 \$\endgroup\$
    – janos
    Sep 11 '14 at 12:59
  • \$\begingroup\$ Yeah, I had actually forgot it, now it's working. Thanks very much for your help :) \$\endgroup\$
    – user46302
    Sep 11 '14 at 13:02
4
\$\begingroup\$

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy