Counting the orders

01 / 04

One row is one order

One row of orders records who bought which item, how many and when.

There are no names in it, only the numbers member_id and item_id. But numbers count perfectly well. Get a sense of the scale first, with no joining.

02 / 04

Orders and units are different

COUNT(*) is how many times something was bought, SUM(quantity) is how many were bought. They look alike and they are not.

One order can be ten of something at once, so watching only the number of orders misleads you about the volume.

SELECT COUNT(*) AS times,       SUM(quantity) AS units  FROM orders;

Result

15 | 45

03 / 04

Gather by number

Per member is GROUP BY member_id, per item is GROUP BY item_id. Even without names, where the peaks are is visible from the numbers alone.

SELECT item_id,       COUNT(*) AS times,       SUM(quantity) AS units  FROM orders  GROUP BY item_id  ORDER BY item_id LIMIT 4;

Result

1 | 2 | 5
2 | 1 | 2
3 | 2 | 5
4 | 1 | 2

04 / 04

Counting how many kinds

Put DISTINCT inside the brackets of COUNT and you get the number of kinds, repeats removed. "How many people ordered?" comes out in one row.

COUNT(member_id) is the number of rows, COUNT(DISTINCT member_id) the number of people. Different things — take care. Let us write some.

SELECT COUNT(member_id) AS rows_seen,       COUNT(DISTINCT member_id)         AS people  FROM orders;

Result

15 | 10