If 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.
(written 2006-01-17, updated 2006-06-09)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S157 - More MySQL commands [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)
[159] MySQL - Optimising Selects - (2004-12-21)
[279] Getting a list of unique values from a MySQL column - (2005-04-14)
[449] Matching in MySQL - (2005-09-24)
[494] MySQL - a score of things to remember - (2005-11-12)
[502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
[513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
[515] MySQL - an FAQ - (2005-12-03)
[517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
[572] Giving the researcher power over database analysis - (2006-01-22)
[581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
[591] Key facts - SQL and MySQL - (2006-02-04)
[673] Helicopter views and tartans - (2006-04-06)
[1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
[1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
[1331] MySQL joins revisited - (2007-09-03)
[1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
[1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
[1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
[2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
[2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
[2448] MySQL - efficiency and other topics - (2009-10-10)
[2643] Relating tables with joins in MySQL - (2010-02-21)
[2644] Counting rows in joined MySQL tables - (2010-02-22)
[2645] Optimising and caching your MySQL enquiries - (2010-02-22)
[2647] Removing duplicates from a MySQL table - (2010-02-22)
[3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
[3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
[4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
Some other Articles
Well House ManorFeatures and BenefitsInstructions for bright peopleNB ''Aspiration''Combining similar rows from a MySQL database selectMay all your screw-ups be big onesUsing PHP to output images, XML, Style sheets, etcOpen Source training from Well House ConsultantsMerging pictures using PHP and GDSmoke and mirrors