PostgreSQL aggregates, GROUP BY and HAVING

Aggregate functions collapse many rows into one value: count, sum, avg, min, max, plus PostgreSQL extras like string_agg, array_agg, bool_and and percentile_cont.

count(*) counts rows; count(column) counts non-NULL values; count(DISTINCT column) counts unique non-NULL values. All aggregates except count(*) ignore NULLs.

GROUP BY splits the rows into groups and computes the aggregates per group. Every column in the select list must either be in GROUP BY or be inside an aggregate. WHERE filters rows before grouping; HAVING filters groups after. The FILTER (WHERE ...) clause lets one aggregate count only some rows.

Logical order of evaluation: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That is why you can’t use a column alias inside WHERE.

Syntax

SELECT group_col, agg_func(col)
FROM table
WHERE row_condition
GROUP BY group_col
HAVING group_condition
ORDER BY ...;

Examples

Edit the code and run it. Output is printed the way psql prints it.

6.1Whole-table summaries

SELECT count(*)            AS employees,
       count(dept_id)      AS with_department,
       round(avg(salary))  AS avg_salary,
       min(hire_date)      AS first_hire,
       max(salary)         AS top_salary
FROM employees;

No GROUP BY, so the whole table is one group and you get exactly one row. count(dept_id) is one lower than count(*) because Lucy has no department.

6.2Group by a column

SELECT category,
       count(*)           AS products,
       sum(stock)         AS units_in_stock,
       round(avg(price),2) AS avg_price
FROM products
GROUP BY category
ORDER BY products DESC;

One output row per category. You may ORDER BY an alias because ordering happens after SELECT.

6.3HAVING filters groups

SELECT customer_id, count(*) AS order_count
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING count(*) >= 2
ORDER BY order_count DESC, customer_id;

WHERE removes cancelled orders before grouping.
GROUP BY customer_id makes one group per customer.
HAVING keeps customers with two or more remaining orders.

6.4FILTER and string_agg

SELECT status,
       count(*) AS orders,
       count(*) FILTER (WHERE order_date >= '2024-07-01') AS since_july,
       string_agg(id::text, ', ' ORDER BY id) AS order_ids
FROM orders
GROUP BY status;

FILTER restricts one aggregate without affecting the others. string_agg joins values into one string; the ORDER BY inside it sorts the pieces. id::text casts the integer to text.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 6.1

For each dept_id (including NULL), show the number of employees as headcount and the average salary rounded to 0 decimals as avg_salary.

Hint

GROUP BY dept_id; round(avg(salary))

Solution

SELECT dept_id, count(*) AS headcount, round(avg(salary)) AS avg_salary FROM employees GROUP BY dept_id;

Exercise 6.2

Show the product category and total_stock for categories whose total stock is more than 200.

Hint

GROUP BY category HAVING sum(stock) > 200

Solution

SELECT category, sum(stock) AS total_stock FROM products GROUP BY category HAVING sum(stock) > 200;

Exercise 6.3

How many units of each product_id were sold in total? Show product_id and units, best sellers first, top 5 only (break ties by product_id).

Hint

sum(quantity) from order_items

Solution

SELECT product_id, sum(quantity) AS units FROM order_items GROUP BY product_id ORDER BY units DESC, product_id LIMIT 5;