GROUP BY explained: why SQL keeps rejecting your columns

One row per group

This query returns one row per customer, with their order count and total spend. customer_id is in the GROUP BY, so it survives; everything else is aggregated.

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;

“Column must appear in the GROUP BY clause”

PostgreSQL and SQL Server reject a query that selects a column they cannot pick a single value for. If you ask for order_date alongside a grouped customer_id, which of the customer's twenty dates should the database show? Either group by it as well, or aggregate it — MAX(order_date) gives the latest.

MySQL used to allow this and return an arbitrary value, which is why old queries break when ONLY_FULL_GROUP_BY is enabled. The fix is the same: aggregate it or group by it.

SELECT customer_id,
       MAX(order_date) AS last_order,
       COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

WHERE filters rows, HAVING filters groups

WHERE runs before grouping, so it cannot see COUNT(*). HAVING runs after, so it can. Filter individual rows in WHERE (it is faster — fewer rows to group) and filter the grouped results in HAVING.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) > 3
ORDER BY order_count DESC;

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT …)

COUNT(*) counts rows. COUNT(column) skips NULLs in that column, which is often what silently makes two counts disagree. COUNT(DISTINCT customer_id) counts unique values — the right choice after a join has duplicated rows.

One more trap: after a LEFT JOIN, COUNT(*) counts the NULL row too, so customers with no orders show 1 instead of 0. Use COUNT(o.id) to get a true zero.