Grouping Data

Grouping is a SQL feature that enables you to produce summary data on groups of rows in the result set.

Consider the first example in this lesson, in which you performed a query using COUNT(*) to find the total number of orders in the database. Suppose that now you want to know the total number of orders for each customer.

By adding a WHERE clause, you could restrict the count using a customer_code value, but you would have to perform this query once for each customer to find all the totals. By grouping data instead, you can perform the aggregate function for each customer in a single query.

The GROUP BY Clause

To tell MySQL how to group the data in a query, you add a GROUP BY clause. This must appear after the WHERE clause and before any ORDER BY. As with the ORDER BY clause, the GROUP BY keywords are followed by one or a list of column names.

The GROUP BY clause collates rows in which the column specified contains the same value so that many table rows are returned as a single row of data. With data grouped in this way, you can add an aggregate function to the SELECT statement, and that function will be evaluated for each table row that was included in the group.

The following query finds the total number of orders for each customer in the database:

 mysql> SELECT customer_code, COUNT(*)     -> FROM orders     -> GROUP BY customer_code; +---------------+----------+ | customer_code | COUNT(*) | +---------------+----------+ | MUSGRP        |        2 | | PRESINC       |        2 | | SCICORP       |        3 | +---------------+----------+ 3 rows in set (0.02 sec) 

Grouping

Without an aggregate function, the GROUP BY clause simply causes MySQL to return only unique rowsjust like the DISTINCT keyword.

Using Column Aliases

When you want to group data on a calculated column, use a column alias to give the column a manageable name. You can give the column alias in the GROUP BY clause.

The following example selects the number of orders in the database grouped by month, using the DATE_FORMAT() function to return only the month and year from the order_date column:

 mysql> SELECT DATE_FORMAT (order_date, '%M %Y') AS order_month,     ->        COUNT(*)     -> FROM orders     -> GROUP BY order_month     -> ORDER BY order_date; +---------------+----------+ | order_month   | COUNT(*) | +---------------+----------+ | January 2006  |        3 | | February 2006 |        4 | +---------------+----------+ 2 rows in set (0.00 sec) 

Although you could specify the full column calculation in the GROUP BY clause, it's clearly more convenient to use a table alias here.

Note that the ORDER BY clause in this query uses order_date, not order_monthyou want the results to be shown in date order, not alphabetically, which would show February before January.

ordering

If you do not supply an ORDER BY clause, the query will be ordered on the columns specified in the GROUP BY clause. If you want, you can also use the column alias in the ORDER BY clause.

Grouping on Several Columns

It's possible to group by more than one column by specifying a comma-separated list of column names in the GROUP BY clause. When you do this, grouping requires that all columns specified contain the same value.

The following example groups data from the orders table by both month and customer. The summary data produced shows a breakdown of the number of orders placed by each customer in each month.

 mysql> SELECT DATE_FORMAT (order_date,'%M %Y') AS order_month,     ->        customer_code,     ->        COUNT(*)     -> FROM orders     -> GROUP BY order_month, customer_code     -> ORDER BY order_date; +---------------+---------------+----------+ | order_month   | customer_code | COUNT(*) | +---------------+---------------+----------+ | January 2006  | PRESINC       |        2 | | January 2006  | SCICORP       |        1 | | February 2006 | MUSGRP        |        2 | | February 2006 | SCICORP       |        2 | +---------------+---------------+----------+ 4 rows in set (0.00 sec) 

The order in which the columns are given in the GROUP BY clause does not affect the grouping. The following example reversed the column order so that customers appear first, followed by a breakdown of their orders by month. Although it would make for more readable code to specify the grouped columns in the same order they are selected, this example shows that it does not actually affect the query results:

 mysql> SELECT customer_code,     ->        DATE_FORMAT (order_date,'%M %Y') AS order_month,     ->        COUNT(*)     -> FROM orders     -> GROUP BY order_month, customer_code     -> ORDER BY customer_code; +---------------+---------------+----------+ | customer_code | order_month   | COUNT(*) | +---------------+---------------+----------+ | MUSGRP        | February 2006 |       2  | | PRESINC       | January 2006  |       2  |  | SCICORP       | January 2006  |       1  | | SCICORP       | February 2006 |       2  | +---------------+---------------+----------+ 4 rows in set (0.00 sec)