Grouping and SortingIt 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.
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. ![]() |