-- Using COUNT(*) with LEFT JOIN gives wrong results

SELECT c.customer_name, COUNT(*) as order_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_name;

-- Use COUNT(column) to ignore NULL values

SELECT c.customer_name, COUNT(o.order_id) as order_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_name;

COUNT(*) counts all rows including NULL. Customers without orders show count=1 instead of 0. COUNT(o.order_id) ignores NULL values.

COUNT(*) counts NULL rows from LEFT JOIN, showing 1 instead of 0

-- Missing columns in GROUP BY causes error

SELECT c.customer_name, c.email, COUNT(o.order_id)

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_name; -- Missing c.email

-- Include all non-aggregated SELECT columns in GROUP BY

SELECT c.customer_name, c.email, COUNT(o.order_id)

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id, c.customer_name, c.email;

SQL requires all SELECT columns (except aggregates) to be in GROUP BY. Include primary key for safety.

All non-aggregated columns must be in GROUP BY clause

-- Using WHERE to filter aggregated results

SELECT c.customer_name, COUNT(o.order_id) as order_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE COUNT(o.order_id) > 5 -- Wrong! Can't use aggregates in WHERE

GROUP BY c.customer_name;

-- Use HAVING to filter aggregated results

SELECT c.customer_name, COUNT(o.order_id) as order_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_name

HAVING COUNT(o.order_id) > 5;

WHERE filters before grouping. Use HAVING to filter after aggregation. HAVING works with aggregate functions.

Cannot use aggregate functions in WHERE clause

-- Double-counting from incorrect join causing inflated totals

SELECT c.customer_name, SUM(p.price) as total_spent