Now that you have completed our MySQL tutorials, you should have learned how to insert, update and delete data from you MySQL tables, along with retrieving it using relationships between tables. Our final guide on MySQL concerns aggregate functions, which allow you to design queries which can provide information you would normally need to use a scripting language to process. First, we need to set up an example table:

The first aggregate functions we will look at are SUM() and GROUP BY(). SUM adds the values of a columns or group together as an additional return field. GROUP BY creates groups on a column, where rows in that column have the same value. We will also introduce the AS operator, which allows us to create alias names for the additional return fields we create (this is very useful when you start writing large, nested queries)

SELECT `genre`, SUM(`price`) AS `total` FROM `albums` GROUP BY(`genre`)

This query will give us the total price of all items in the table, after they have been split into groups where their `genre` column has the same value. Our results will be:

There are a number of aggregate functions which can be used in the same way: AVG(), MIN(), MAX() and COUNT():

SELECT `genre`, MAX(`price`) AS `max` FROM `albums` GROUP BY(`genre`)
SELECT `genre`, MIN(`price`) AS `min` FROM `albums` GROUP BY(`genre`)
SELECT `genre`, AVG(`price`) AS `avg` FROM `albums` GROUP BY(`genre`)
SELECT `genre`, COUNT(`title`) AS `number` FROM `albums` GROUP BY(`genre`)