Grouping and Sorting

It is important to understand that GROUP BY and ORDER BY are very different, even though they often accomplish the same thing. Table 13.1 summarizes the differences between them.

Table 13.1. ORDER BY Versus GROUP BY
ORDER BY
GROUP BY
Sorts generated output.
Groups rows. The output might not be in group order, however.
Any columns (even columns not selected) may be used.
Only selected columns or expressions columns may be used, and every selected column expression must be used.
Never required.
Required if using columns (or expressions) with aggregate functions.

The first difference listed in Table 13.1 is extremely important. More often than not, you will find that data grouped using GROUP BY will indeed be output in group order. But that is not always the case, and it is not actually required by the SQL specifications. Furthermore, you might actually want it sorted differently than it is grouped. Just because you group data one way (to obtain group-specific aggregate values) does not mean that you want the output sorted that same way. You should always provide an explicit ORDER BY clause as well, even if it is identical to the GROUP BY clause.

Tip

Don't Forget ORDER BY As a rule, anytime you use a GROUP BY clause, you should also specify an ORDER BY clause. That is the only way to ensure that data is sorted properly. Never rely on GROUP BY to sort your data.

To demonstrate the use of both GROUP BY and ORDER BY, let's look at an example. The following SELECT statement is similar to the ones seen previously. It retrieves the order number and total order price of all orders with a total price of 50 or more:

• Input

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50;

• Output

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 | 149.87     |
|     20006 | 55.00      |
|     20007 | 1000.00    |
|     20008 | 125.00     |
+-----------+------------+

To sort the output by order total, all you need to do is add an ORDER BY clause, as follows:

• Input

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

• Output

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 | 55.00      |
|     20008 | 125.00     |
|     20005 | 149.87     |
|     20007 | 1000.00    |
+-----------+------------+

• Analysis

In this example, the GROUP BY clause is used to group the data by order number (the order_num column) so that the SUM(*) function can return the total order price. The HAVING clause filters the data so that only orders with a total price of 50 or more are returned. Finally, the output is sorted using the ORDER BY clause.

Grouping and Sorting

Table 13.1. ORDER BY Versus GROUP BY

Table 13.1. `ORDER BY` Versus `GROUP BY`