A report needs each order with the number of line items it contains. Your query returns a wildly inflated count. Why, and what is the fix?
Because the join multiplied the rows, and the count is counting the multiplied rows.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
placed_on TEXT NOT NULL
);
CREATE TABLE order_lines (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
product TEXT NOT NULL,
quantity INTEGER NOT NULL
);
So what is happening? The order has three lines in it, so the join produces three rows for that one order. The function COUNT(*) counts the rows of the joined answer, so it says three for that order, which is right for the lines and wrong for the orders.
And the real damage comes when two orders are joined together. An order with three lines and an order with five lines give eight rows between them, so a count of the orders says eight when there are only two.
-- The fix: count the different lines.
SELECT
o.id,
o.customer,
COUNT(l.id) AS line_count
FROM orders o
LEFT JOIN order_lines l ON l.order_id = o.id
GROUP BY o.id, o.customer
ORDER BY o.id;
There is a second fix, and it is the better one when the table is big. You work out the count of the lines in its own step, and then you join that small answer onto the orders.
-- The fix: work out the count first, then join it on.
WITH line_counts AS (
SELECT order_id, COUNT(*) AS line_count
FROM order_lines
GROUP BY order_id
)
SELECT
o.id,
o.customer,
COALESCE(lc.line_count, 0) AS line_count
FROM orders o
LEFT JOIN line_counts lc ON lc.order_id = o.id
ORDER BY o.id;
So the rule to remember is this. When you count something across a join, count the different identifiers and not the rows, or work the count out before the join.
The last question in this topic looks at the query that everybody writes and everybody gets wrong.