Grouping Data - MariaDB Crash Course - page 108

Grouping and Sorting
It is important to understand

that GROUP BY and ORDER BY are different, even
though they often accomplish the same thing. Table 13.1 summarizes the dif-
ferences 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 Only selected columns or expressions columns

not selected) may be used. 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 differ-
ently 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 exam-
ple. 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;