Grouping results (aggregate functions)

You can group the results using the GROUP BY clause on a column and then use AGGREGATE functions, such as COUNT, MAX, MIN, and AVERAGE. You can also use the function on a column in a group by clause. See the SUM example where you will use the YEAR() function.

How to do it...

Each of the previously-mentioned aggregate functions will be introduced to you here in detail.

COUNT

1. Find the count of male and female employees:

mysql> SELECT gender, COUNT(*) AS count FROM employees GROUP BY gender;
+--------+--------+
| gender | count  |
+--------+--------+
| M      | 179973 |
| F      | 120051 |
+--------+--------+
2 rows in set (0.14 sec)

2. You want to find the 10 most common first names of the employees. You can use GROUP BY first_name to group all the first names, then COUNT(first_name) to find the count inside the group, and finally the ORDER BY count to sort the results. LIMIT these results to the top 10:

mysql> SELECT first_name, COUNT(first_name) AS count...