Assuming I have a table below:
<pre>
<table>
<tr>
<td><b>id</b></td>
<td><b>name</b></td>
<td><b>rank</b></td>
</tr>
<tr>
<td>A</td>
<td>Chicago</td>
<td>4</td>
</tr>
<tr>
<td>B</td>
<td>New York</td>
<td>3</td>
</tr>
<tr>
<td>A</td>
<td>Chicago</td>
<td>5</td>
</tr>
<tr>
<td>C</td>
<td>Ohio</td>
<td>2</td>
</tr>
<tr>
<td>A</td>
<td>Chicago</td>
<td>3</td>
</tr>
</table>
</pre>
Where the id column relates all the rows.
How can I merge all the rows having the same id value together and the rank column will be the average of all the rows being merged.
E.g the result will look like the table below:
<pre>
<table>
<tr>
<td><b>id</b></td>
<td><b>name</b></td>
<td><b>rank</b></td>
</tr>
<tr>
<td>A</td>
<td>Chicago</td>
<td>4</td>
</tr>
<tr>
<td>B</td>
<td>New York</td>
<td>3</td>
</tr>
<tr>
<td>C</td>
<td>Ohio</td>
<td>2</td>
</tr>
</table>
</pre>
Where 4 in the rank column is the average sum of all the rows merge in the first table
Note: Each row is an array taken from a 2D array. Therefore the result must be a 2D array.
Using SQL is just too hard as this table comprises of 4 other tables from the database. So I'd prefer the answer in PHP or JavaScript
Thanks
How about
SELECT id, name, avg(rank) FROM table GROUP BY id, name;
The key here is the group by.
As you said your table is created by joining four tables you can use it with a subselect:
SELECT id, name, avg(rank) FROM
(
SELECT * FROM whatver --here goes your statement joining the four tables
)
GROUP BY id, name;
SELECT `id`, `name`, AVG( rank ) AS `rank` FROM table_name GROUP BY `id`, `name`
SELECT ID,NAME,AVG([RANK])[RANK] FROM TABLE GROUP BY ID,NAME