Join, then narrow and count

01 / 04

After joining, it is business as usual

You can treat a joined result as one big table. So WHERE, ORDER BY and GROUP BY all lay straight over it.

The writing order is FROM → JOIN → ON → WHERE → GROUP BY → ORDER BY. The clauses you already know follow on behind the ON.

SELECT items.name, orders.quantity  FROM orders  JOIN items  ON orders.item_id = items.id  WHERE items.category = 'table'  ORDER BY orders.quantity DESC;

Result

bean coaster | 6
sunrise mug | 4
sunrise mug | 3
bean coaster | 3
cypress chopstick rest | 2
cypress chopstick rest | 2

02 / 04

Calculating across two tables

Once joined, you can multiply values that lived in separate tables. The price is in items and the quantity is in orders, so until you joined them there was no way to work out what an order came to.

Give the calculated column a name with AS. ORDER BY can use that name too.

SELECT items.name,  items.price * orders.quantity    AS amount  FROM orders  JOIN items  ON orders.item_id = items.id  ORDER BY amount DESC;

Result

sunrise mug | 6400
sunrise mug | 4800
dappled light lamp | 3800
bean coaster | 1560
cotton dishcloth | 1360
cypress chopstick rest | 960
cypress chopstick rest | 960
bean coaster | 780
cotton dishcloth | 680

03 / 04

Gathering by name

Hand items.name to GROUP BY and you can gather by item name. Before, you could only gather by item_id, so this reads a great deal better.

The column being aggregated is on the orders side, the column being gathered by is on the items side. Spanning tables changes nothing about how GROUP BY is used.

SELECT items.name,  SUM(orders.quantity) AS total  FROM orders  JOIN items  ON orders.item_id = items.id  GROUP BY items.name  ORDER BY total DESC;

Result

bean coaster | 9
sunrise mug | 7
cotton dishcloth | 6
cypress chopstick rest | 4
dappled light lamp | 1

04 / 04

Takings per kind

Put the multiplication and the GROUP BY together and you get takings per kind. At this point it is a table you could take into a meeting about the shop.

Do notice that "decor" is missing from the result. The vase has never been ordered, so it vanished in the join. Let us write some.

SELECT items.category,  SUM(items.price * orders.quantity)    AS sales  FROM orders  JOIN items  ON orders.item_id = items.id  GROUP BY items.category  ORDER BY sales DESC;

Result

table | 15460
lighting | 3800
kitchen | 2040