Finding The Avg Of A Sum

[Steven]

I looked up the AVG and SUM tutorials on W3 and I can understand it. But, what do I do if I want the SUM of certain fields in a row, and not in a column? For example: http://www.ngaguild.com/steve/thphp/What I am trying to do here is list all players on a hockey roster with all their attribute scores and then find the averages of the different types of scores. By different types of scores I mean find the averages for the ability scores most important to players who pass the puck, players who shoot the puck and players who defend the puck. Each type of player requires a different set of good stats. What I want to do is sum up and average out each set of stats to find which ones are best where.In order to do this I need to find the sum of rows, not columns. For example, the first row is Samuel Carter, a left defenseman. To find his average defensive score, I would want to find the average of his Positioning, Checking, Hitting and Passing scores; to find his offensive goal scoring score, I would want to find the average of his Speed, Slap Shot, Wrist Shot, Puck Control, etc. scores. Make sense?Thank you so much in advance,Steven

[justsomeguy]

The SQL functions operate over columns, if you want to operate over a row it's probably best to just do it in PHP. You can do math in SQL, but the aggregate functions operate over columns for multiple rows.SELECT (col1 + col2 + col3 + col4) / 4 AS col_avg ...

[Steven]

Hmm. With PHP then, would I somehow grab from the DB based on the primary key id and turn it into an array? And if so... how? What tutorial would help with that.

[justsomeguy]

You just use the addition and division operators. You posted this in the PHP forum, so I assumed you knew how to get records from MySQL and display them. If you don't know how to do that, check the PHP tutorials on the site.

[Steven]

I tried this: $center = mysql_query("SELECT (Positioning + Passing + Puck Control + Face-Off) / 4 AS col_avg"); but the new Center AVG cell I made is blank.Then I tried wrapping each column name in '' and then the new Center AVG cell returns "Resource id #5".

[justsomeguy]

What code are you using? Does your code print the "col_avg" field? That's the name of the actual field in the result set. If you're seeing a resource ID get printed it sounds like you're trying to print the return value from mysql_query. You need to fetch the row first.

[Steven]

Hmm, it appears as though I am thoroughly confused!Here's php code fetched from the markup:

$str = '';$result = mysql_query("SELECT * FROM th_players");$center = mysql_query("SELECT ('Positioning' + 'Passing' + 'Puck Control' + 'Face-Off') / 4 AS col_avg");while($row = mysql_fetch_array($result)) {	$str .= '<tr>';	$str .= '<td>' . $row['ID'] . '</td>';	$str .= '<td>' . $row['No'] . '</td>';	$str .= '<td>' . $row['First Name'] . '</td>';	$str .= '<td>' . $row['Last Name'] . '</td>';	$str .= '<td>' . $row['Nationality'] . '</td>';	$str .= '<td>' . $row['Age'] . '</td>';	$str .= '<td>' . $row['Position'] . '</td>';	$str .= '<td>' . $row['Strength'] . '</td>';	$str .= '<td>' . $row['Stamina'] . '</td>';	$str .= '<td>' . $row['Skating'] . '</td>';	$str .= '<td>' . $row['Speed'] . '</td>';	$str .= '<td>' . $row['Checking'] . '</td>';	$str .= '<td>' . $row['Hitting'] . '</td>';	$str .= '<td>' . $row['Work Rate'] . '</td>';	$str .= '<td>' . $row['Positioning'] . '</td>';	$str .= '<td>' . $row['Passing'] . '</td>';	$str .= '<td>' . $row['Puck Control'] . '</td>';	$str .= '<td>' . $row['Deking'] . '</td>';	$str .= '<td>' . $row['Slap Shot'] . '</td>';	$str .= '<td>' . $row['Wrist Shot'] . '</td>';	$str .= '<td>' . $row['Face-Off'] . '</td>';	$str .= '<td>' . $center . '</td>';	$str .= '</tr>'; }echo $str;

And here's the markup:

<table width="100%" border="1">  <tr>	<td>ID</td>	<td>No.</td>	<td>First Name</td>	<td>Last Name</td>	<td>Nationality</td>	<td>Age</td>	<td>Position</td>	<td>Strength</td>	<td>Stamina</td>	<td>Skating</td>	<td>Speed</td>	<td>Checking</td>	<td>Hitting</td>	<td>Work Rate</td>	<td>Positioning</td>	<td>Passing</td>	<td>Puck Control</td>	<td>Deking</td>	<td>Slap Shot</td>	<td>Wrist Shot</td>	<td>Face-Off</td>	<td>Center AVG</td>  </tr><?php include("getplayers.php"); ?></table>

[Steven]

Oh my! You're right. I realized I wasn't fetching it FROM anywhere. I tried putting in FROM th_players, but it still stays the same. I tried putting FROM before and after the AS col_avg, I'm not sure which one is correct.

[justsomeguy]

The query should be like this. Make sure not to quote column names. If you're using a reserved word as a column name, you would surround it with `backquotes`, but not the regular single quotes. Since face-off has a hyphen in it, you need to quote it, a hyphen is a subtraction operator. Same with puck control, which has a space. Also, since you're running 2 queries, it will be best to order them by the same field so that you know they'll be in the same order. Notice how you use mysql_fetch_array to get a single row from the result, you need to do the same with the other query.

$result = mysql_query("SELECT * FROM th_players ORDER BY ID ASC");$center = mysql_query("SELECT (Positioning + Passing + `Puck Control` + `Face-Off`) / 4 AS col_avg FROM th_players ORDER BY ID ASC");while($row = mysql_fetch_array($result)) {	$str .= '<tr>';	$str .= '<td>' . $row['ID'] . '</td>';		...	$avg = mysql_fetch_assoc($center);	$str .= '<td>' . $avg['col_avg'] . '</td>';	...}

Since you're already selecting everything from the table though, it's probably better to do the math in PHP instead.

$result = mysql_query("SELECT * FROM th_players ORDER BY ID ASC");while($row = mysql_fetch_array($result)) {	$str .= '<tr>';	$str .= '<td>' . $row['ID'] . '</td>';		...	$str .= '<td>' . ($row['Positioning'] + $row['Passing'] + $row['Puck Control'] + $row['Face-Off']) / 4 . '</td>';	...}

[Steven]

Cool! Thanks a lot, Crazy Old Man From Scene 24. I used PHP for the math and it worked perfectly.What is the best way to form a link with the headers of each column, so that when you click them it sorts that column, similar to how it is set to order by id asc by default?

[justsomeguy]

Add links that tell the PHP which column to sort by, and add some PHP code to check for that and add it to the query.<a href="page.php?sort=name">

[Steven]

I'm not sure what to use for "name" in your example. I tried to use the sql column name, but it doesn't do anything on a click.

[Synook]

//...$sort = mysql_real_escape_string($_GET['sort']);$result = mysql_query("SELECT * FROM th_players ORDER BY $sort ASC");//...

You use the qs var in the ORDER BY column.