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 03:43:35)

 S157 - More MySQL Commands

Back to May all your screw-ups be big ones

Previous and next or Horse's mouth home

Forward to NB ''Aspiration''

Well House Manor
Features and Benefits
Instructions for bright people
NB ''Aspiration''
Combining similar rows from a MySQL database select
May all your screw-ups be big ones
Using PHP to output images, XML, Style sheets, etc
Open Source training from Well House Consultants
Merging pictures using PHP and GD
Smoke and mirrors
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).