Why COUNT and SUM are too high after a JOIN

A total comes out too large. Nothing errors. This is usually join fan-out: the join changed what one row means, and the aggregate kept adding as if it hadn't.

Here's customer 1. In the practice database, logins.user_id is the customer id. We want their number of orders and their number of login days.

SELECT COUNT(*) AS orders FROM orders WHERE customer_id = 1;

SELECT COUNT(*) AS login_days FROM logins WHERE user_id = 1;

Now the obvious single query:

SELECT c.name,
       COUNT(o.id) AS orders,
       COUNT(l.login_date) AS login_days
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN logins l ON l.user_id = c.id
WHERE c.id = 1
GROUP BY c.name;

Both columns report the same inflated number, which is the two true counts multiplied together.

Why it happens

orders and logins are unrelated to each other, and each has many rows per customer. The database pairs every order with every login for that customer. SUM has the same problem: each order's value would be added once per login.

Before you aggregate, ask what one row of the joined result represents. Here it is "one order paired with one login", a thing nobody wants to count.

Fix: aggregate each table before joining

Bring each child table down to one row per customer, then join on that key. Nothing can fan out.

WITH o AS (
  SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id
), l AS (
  SELECT user_id, COUNT(*) AS login_days FROM logins GROUP BY user_id
)
SELECT c.name, o.orders, l.login_days
FROM customers c
JOIN o ON o.customer_id = c.id
JOIN l ON l.user_id = c.id
WHERE c.id = 1;

What about COUNT(DISTINCT)?

COUNT(DISTINCT o.id) repairs a count, and people reach for it first. It can't repair a SUM, because two different orders can legitimately have the same value, and SUM(DISTINCT ...) would collapse them. It also hides the fact that your join produces rows you didn't mean to create.

A habit that catches this

After any join, compare COUNT(*) with the row count of the table you think you're reporting on. If they differ and you can't say why, stop before you aggregate anything.