Calculate average of column from MYSQL query

Question

I've got a table that I am trying to calculate the average of the values in a column. Here is my lookup:

SELECT SUM(P1_Score) AS value_sum FROM tblMatches Where P1_ID LIKE $playerID

Any idea how I can determine the average (sum of values / total rows)?

Oddly enough, there's an analytic called: AVG that does just that :D [link](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html) — xQbert, Jan 06 '12 at 02:35
FYI - You should be using PDO, and not exposing your MySQL queries to raw variables. — Chuck Le Butt, Jan 21 '20 at 15:05

score 91 · Accepted Answer · edited Jan 21 '20 at 15:04

91

You can use AVG like so:

SELECT AVG(P1_Score)

edited Jan 21 '20 at 15:04

Chuck Le Butt

47,570
62
203
289

answered Jan 06 '12 at 02:36

zerkms

249,484
69
436
539

Thanks...its actually a statistics page where filter for a player and view the results of the matches. So I would want to show both the total number of games and average number of games per match. – DoubleA Jan 06 '12 at 02:51
@DoubleA: perform different queries then. They have nothing in common – zerkms Jan 06 '12 at 03:07
Sounds like in the case where you need the total and average for the same set, you might want to use `SELECT COUNT(*), SUM(P1_Score)`, from which you could pull out the sum/total and derive the average. With frameworks like Rails where you can only send one query at a time to your database, assuming your database is on another server, that combined query would save you one round trip. – maurice Feb 04 '16 at 22:49
1

he doesn't need two queries just `SELECT SUM(P1_Score), AVG(P1_SCORE) ...` – Michael May 26 '19 at 21:23
@Michael thanks for clarification, I did not mean they need 2 queries though – zerkms May 26 '19 at 21:55

xQbert · Answer 2 · 2017-09-07T14:59:55.113

9

So in your case:

$gameswon = mysql_query("SELECT AVG(P1_Score) AS value_sum 
                         FROM tblMatches 
                         WHERE P1_ID LIKE '".$playerid."'");

edited Sep 07 '17 at 14:59

answered Jan 06 '12 at 02:36

xQbert

34,733
2
41
62

GiantRobot · Answer 3 · 2012-01-06T02:41:34.143

5

Try using AVG() aggregate function instead of SUM

$gameswon = mysql_query("SELECT AVG(P1_Score) AS value_sum FROM tblMatches Where P1_ID LIKE '".$playerid."' . "GROUP BY XXXX");

and XXXX is the column that you want to get average for such as player

edited Jan 06 '12 at 02:41

answered Jan 06 '12 at 02:36

GiantRobot

432
3
6

Calculate average of column from MYSQL query

3 Answers3

Linked

Related