Practice Some More Queries

Let’s take a look at groups and subqueries.

Show customers along with their order totals

We need to come back with a result like the following:

+-------------+-----------+----------+--------------+----------+------------------+
| customer_id | name      | identity | order_number | order_id | amount_per_order |
+-------------+-----------+----------+--------------+----------+------------------+
|           4 | John Woo  | JW001    | ABC001       |        2 |           100.00 |
|           5 | Maria Foo | MF001    | ABC002       |        3 |            69.80 |
|           5 | Maria Foo | MF001    | ABC003       |        4 |            99.00 |
|           6 | Jim Papas | JP001    | ABC004       |        5 |             5.00 |
|           6 | Jim Papas | JP001    | ABC005       |        6 |            10.00 |
|           6 | Jim Papas | JP001    | ABC006       |        7 |            19.80 |
+-------------+-----------+----------+--------------+----------+------------------+
6 rows in set (0.00 sec)

Let’s take this slow. We initially see that it contains information from the customers table:

mysql> select customers.id as customer_id, customers.name, customers.identity from customers;
+-------------+-------------+----------+
| customer_id | name        | identity |
+-------------+-------------+----------+
|           4 | John Woo    | JW001    |
|           5 | Maria Foo   | MF001    |
|           6 | Jim Papas   | JP001    |
|           7 | Jessy Romeo | JR001    |
|           8 | Arya Stark  | AS001    |
+-------------+-------------+----------+
5 rows in set (0.00 sec)

We used the verbose way to enlist the properties of the customers because we had to alias the customers.id to customer_id. The desired result set references the order_number and the order_id. Cool! We can get them from the orders table, of course, by joining:

mysql> select customers.id as customer_id, customers.name, customers.identity, orders.order_number, orders.id as order_id from customers join orders on orders.customer_id = customers.id;
+-------------+-----------+----------+--------------+----------+
| customer_id | name      | identity | order_number | order_id |
+-------------+-----------+----------+------------