SELECT - Grouping results

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SELECT [flags] {*|column|expression}[, ...]
FROM table[, ...]
[WHERE condition]
[GROUP BY {column|expression|position}[ASC|DESC], ...
  [WITH ROLLUP]]
[other clauses] [options]

Explanation

A SELECT statement sometimes produces more meaningful results if you group together rows containing the same value for a particular column. The GROUP BY clause specifies one or more columns by which MySQL is to group the data retrieved. This is used with aggregate functions so that the values of numeric columns for the rows grouped will be aggregated.

Examples

As an example, suppose that a SELECT statement is to list the sales representatives for a business and their orders for the month. Without a GROUP BY clause, one line would be displayed for each sales representative for each order.

Here's an example of how this might be resolved:

SELECT CONCAT(name_first, ' ', name_last) AS 'Sales Rep.',
SUM(total_order) AS 'Sales for Month'
FROM orders, employees
WHERE employees.emp_id = sales_rep
AND MONTH(order_date) = MONTH(CURDATE( ))
GROUP BY sales_rep;

This MySQL statement concatenates the first and last name of each sales representative who placed an order for a customer during the current month. The GROUP BY clause groups together the rows found for each sales representative. The SUM( ) function adds the values of the total_order column for each row within each group.

You can specify multiple columns in the GROUP BY clause. Instead of stating a column's name, you can state its position in the MySQL table, where a value of 1 represents the first column in the table. Expressions may be given, as well.

The GROUP BY clause does its own sorting and cannot be used with the ORDER BY clause. To set the sorting to ascending order explicitly for a column, enter the ASC keyword after the column in the clause which is to be set. This is not necessary, though, since it is the default setting. To sort in descending order, add DESC after each column that is to be sorted in reverse.

When grouping rows by one column, it may be desirable not only to have a total of the values for certain columns, but also to display a total for all of the grouped rows at the end of the results set. To do this, use the WITH ROLLUP keyword. Here is an example:

SELECT location AS Branch,
CONCAT(name_first, ' ', name_last) AS 'Sales Rep.',
SUM(total_order) AS 'Sales for Month'
FROM orders, employees, branches
WHERE sales_rep = employees.emp_id
AND MONTH(order_date) = MONTH(CURDATE( ))
AND employees.branch_id = branches.branch_id
GROUP BY Branch, sales_rep WITH ROLLUP;

+---------------+---------------+-----------------+
| Branch        | Sales Rep.    | Sales for Month |
+---------------+---------------+-----------------+
| Boston        | Sean Wilson   |            2472 |
| Boston        | Morgan Miller |            1600 |
| Boston        | Morgan Miller |            4072 |
| New Orleans   | Marie Sims    |            1750 |
| New Orleans   | Tom Smith     |            6407 |
| New Orleans   | Sean Johnson  |            5722 |
| New Orleans   | Sean Johnson  |           13879 |
| San Francisco | Geoffrey Frey |             500 |
| San Francisco | Kenneth Thaw  |             500 |
| San Francisco | Kenneth Thaw  |            1000 |
| NULL          | Kenneth Thaw  |           18951 |
+---------------+---------------+-----------------+

This MySQL statement groups and adds up the total for each sales representative. When there aren't any more sales representatives for a branch, a row in the display for the subtotal is generated. It displays the branch name and the name of the last representative. When there are no more branches, a row for the grand total of sales is generated. The branch shows NULL. For clarity, I've boldfaced the subtotals and the grand total in the results set.