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 |
+-------------+-----------+----------+--------------+----------+
|           4 | John Woo  | JW001    | ABC001       |        2 |
|           5 | Maria Foo | MF001    | ABC002       |        3 |
|           5 | Maria Foo | MF001    | ABC003       |        4 |
|           6 | Jim Papas | JP001    | ABC004       |        5 |
|           6 | Jim Papas | JP001    | ABC005       |        6 |
|           6 | Jim Papas | JP001    | ABC006       |        7 |
+-------------+-----------+----------+--------------+----------+
6 rows in set (0.00 sec)

We know all this pretty well by now. We joined with orders using the relationship of customers and orders. Then, we picked up the columns of orders that we wanted and aliased them to label names, order_number, and order_id.

We’re now missing the amount_per_order column. Remember that we got this column earlier using the following query:

mysql> select order_items.order_id, sum(price * quantity) as amount_per_order from order_items group by order_id;
+----------+------------------+
| order_id | amount_per_order |
+----------+------------------+
|        2 |           100.00 |
|        3 |            69.80 |
|        4 |            99.00 |
|        5 |             5.00 |
|        6 |            10.00 |
|        7 |            19.80 |
+----------+------------------+
6 rows in set (0.00 sec)

As we can see in the picture, our objective is basically to join information from two different queries.