Group-Based Aggregation

Why do we need to group?

This is going to be a little bit more difficult, but we’ll answer this question in small steps.

If we list the orders_items, we see that we have the price and the quantity columns.

mysql> select * from order_items;
+----+----------+------------+-------+----------+
| id | order_id | product_id | price | quantity |
+----+----------+------------+-------+----------+
|  4 |        2 |          1 | 50.00 |        2 |
|  5 |        3 |          1 | 50.00 |        1 |
|  6 |        3 |          2 | 19.80 |        1 |
|  7 |        4 |          2 | 19.80 |        5 |
|  8 |        5 |          3 |  5.00 |        1 |
|  9 |        6 |          3 |  5.00 |        2 |
| 10 |        7 |          2 | 19.80 |        1 |
+----+----------+------------+-------+----------+
7 rows in set (0.00 sec)

The total amount of the first order with an id value of 2 is 2 x 50.00 = 100.00. The total amount of the order with an id value of 3 is 1 x 50.00 + 1 x 19.80 = 69.80, and the total amount of the order with an id value of 4 is 5 x 19.80 = 99.00.

This means that the order_items ...