Aggregating data

This is one of the most powerful aspects of the SQL language. To do this, we use the GROUP BY clause in a SELECT statement. This groups one or more rows together and reports values based on this group. MySQL has many functions that operate on a group of rows, one of which is MAX(), which gets the maximum value from the group. It is important to only ever use the columns on which you are grouping by and/or other columns with an aggregate function.

Consider this data in the following table:

Figure 4.23 – The sales table

Consider the following query:

SELECT region, SUM(sales) FROM sales GROUP BY region;

This outputs the following results:

Figure 4.24 – The SELECT output, demonstrating GROUP BY

This groups the rows by region, creating two groups, and then it sums the rows in each group.

Now, consider this query:

SELECT city, SUM(sales) FROM sales GROUP BY region;

This outputs the following...