Database Reference
In-Depth Information
▼
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.
This is probably a good time to review the order in which
SELECT
statement
clauses are to be specified. Table 13.2 lists all the clauses you have learned thus
far, in the order they must be used.