Using the GROUP BY Clause

The next clause we will look at is the GROUP BY clause. This allows us to consider retrieved rows in groups. This is really useful only when we use it in combination with functions that operate over a group of rows. The only one of these we have mentioned so far is count(), but we will look at many more in Chapter 8.

Consider the following query:

select count(*), job
from employee
group by job;

This query will count the number of employees in each job grouping—that is, the number of employees who hold each job. If you run this query on the employee database, you should see a result similar to the following:

+----------+-----------------------+
| count(*) | job |
+----------+-----------------------+
| 1 | DBA |
| 2 | Programmer |
| 1 | Systems Administrator |
+----------+-----------------------+
3 rows in set (0.04 sec)

There are two differences between the way GROUP BY works in MySQL and ANSI SQL.

In ANSI SQL, you must group by all the columns you have listed in the initial SELECT clause. MySQL allows you to have additional fields in the SELECT clause that are not in the GROUP BY clause.

MySQL also allows you to sort the group order in which the results are presented. The default order is ascending. If we want to repeat the last query but see the results in descending order, we can use the following query:

select count(*), job
from employee
group by job desc;

This will produce results similar to the following:

+----------+-----------------------+
| count(*) | job |
+----------+-----------------------+
| 1 | Systems Administrator |
| 2 | Programmer |
| 1 | DBA |
+----------+-----------------------+
3 rows in set (0.04 sec)

As you can see, the names of the jobs are now in reverse alphabetical order. You can also specify ASC (for ascending), but this is the default, so it's redundant to do so.