Combining similar rows from a MySQL database select
Archive - Originally posted on "The Horse's Mouth" - 2006-01-17 03:43:35 - Graham EllisIf you're selecting data from a MySQL database and you want to report back the total or average for a column, you can use a function such as avg or sum on the column. If you want to choose only certain records to include in your sum or average, add a WHERE clause and if you want to produce a separate sum or average for each unique value in another column, use a GROUP BY clause.
Basic data:
mysql> select agid, asking from sales where agid > 9;
+------+--------+
| agid | asking |
+------+--------+
| 10 | 225000 |
| 10 | 195000 |
| 10 | 237500 |
| 11 | 465000 |
| 11 | 275000 |
+------+--------+
Summing all the asking prices:
mysql> select sum(asking) from sales where agid > 9;
+-------------+
| sum(asking) |
+-------------+
| 1397500 |
+-------------+
Summing the asking prices agid by agid:
mysql> select agid, sum(asking) from sales where agid > 9 group by agid;
+------+-------------+
| agid | sum(asking) |
+------+-------------+
| 10 | 657500 |
| 11 | 740000 |
+------+-------------+
See [url=http://www.wellho.net/mouth/515_MySQL-an-FAQ.html]our MySQL FAQ[/url] for other hints on MySQL SELECT commands.