GROUP BY, HAVING and aggregations

Aggregating means turning many rows into one per group. Aggregate functions ignore NULL, with one exception: COUNT(*), which counts rows.

SELECT COUNT(*) AS row_count,
       COUNT(amount) AS with_amount,
       SUM(amount) AS total,
       AVG(amount) AS average
FROM orders;

row_count | with_amount | total | average
--------- | ----------- | ----- | -------
10        | 9           | 3600  | 400

The average ignored the order with no amount: it is SUM(amount) / COUNT(amount), not divided by all the rows. If "no amount" should count as zero, say so with COALESCE(amount, 0).

HAVING filters groups

WHERE runs before grouping; HAVING runs after. A filter on the result of an aggregate can only go in HAVING.

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE status <> 'canceled'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY total DESC;

customer_id | orders | total
----------- | ------ | -----
1           | 2      | 1130
2           | 2      | 1050
3           | 2      | 180

NULL is a group

To GROUP BY, all the NULLs fall into the same group (even though NULL = NULL is not true).

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC, city;

city      | customers
--------- | ---------
São Paulo | 2
NULL      | 1
Campinas  | 1
Curitiba  | 1
Recife    | 1

COUNT(DISTINCT)

SELECT COUNT(DISTINCT customer_id) AS distinct_customer_ids,
       COUNT(DISTINCT order_date) AS days_with_sales
FROM orders;

distinct_customer_ids | days_with_sales
--------------------- | ---------------
6                     | 5

That is 6 ids, not 5 real customers: the 9 from the orphan order is counted. COUNT(DISTINCT) does not know whether the key exists in the other table; that is a job for a JOIN or a quality rule.

• At large volumes, exact COUNT(DISTINCT) is expensive; Spark and Databricks have approx_count_distinct (HyperLogLog), with a controlled error.
• Every SELECT column that is not inside an aggregate must be in the GROUP BY (SQLite lets it slide, the standard and Spark do not).