PostgreSQL Zero to Advanced #5: Aggregation, GROUP BY & HAVING

Welcome to the intermediate section of the series. So far every query returned individual rows. But the questions your boss asks are usually about totals: How much revenue did we make? Which month was best? Who are our top customers? In this part we answer them with aggregate functions, GROUP BY and HAVING, using the 14 orders we inserted in Part 3.

Aggregate functions

An aggregate function takes many rows and returns a single value. The five you will use daily are COUNT, SUM, AVG, MIN and MAX.

COUNT(*) vs COUNT(column)

COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL. That difference is a classic source of confusion:

SELECT COUNT(*)    AS total_users,
       COUNT(city) AS users_with_city
FROM users;

Output:

 total_users | users_with_city 
-------------+-----------------
           8 |               7
(1 row)

We have 8 users, but only 7 of them have a city because Agus' city is NULL.

SUM, AVG, MIN, MAX

Let's measure our paid orders. Refunded and pending orders are not real revenue, so we filter them out with WHERE before aggregating:

SELECT SUM(amount)        AS revenue,
       ROUND(AVG(amount)) AS avg_order,
       MIN(amount)        AS cheapest,
       MAX(amount)        AS most_expensive
FROM orders
WHERE status = 'paid';

Output:

 revenue | avg_order | cheapest | most_expensive 
---------+-----------+----------+----------------
 2665000 |    242273 |   120000 |         400000
(1 row)

AVG on integers returns a NUMERIC with many decimals (242272.727...), so we wrap it in ROUND(). Use ROUND(AVG(amount), 2) if you need two decimal places.

GROUP BY: one result per group

GROUP BY splits rows into groups that share the same value and runs the aggregate once per group. How many orders and how much money per status?

SELECT status,
       COUNT(*)    AS total_orders,
       SUM(amount) AS total_amount
FROM orders
GROUP BY status
ORDER BY total_orders DESC;

Output:

  status  | total_orders | total_amount 
----------+--------------+--------------
 paid     |           11 |      2665000
 pending  |            2 |       650000
 refunded |            1 |       250000
(3 rows)

Notice the totals: 11 + 2 + 1 = 14 orders, and 2,665,000 is the same paid revenue we calculated above. Grouping courses by category works the same way:

SELECT category,
       COUNT(*)          AS courses,
       MIN(price)        AS min_price,
       MAX(price)        AS max_price,
       ROUND(AVG(price)) AS avg_price
FROM courses
GROUP BY category
ORDER BY category;

Output:

 category | courses | min_price | max_price | avg_price 
----------+---------+-----------+-----------+-----------
 data     |       1 |    300000 |    300000 |    300000
 database |       3 |    150000 |    350000 |    258333
 devops   |       1 |    200000 |    200000 |    200000
 web      |       3 |    120000 |    400000 |    256667
(4 rows)

Now the classic "top customers" report. We group paid orders by user_id:

SELECT user_id,
       COUNT(*)    AS paid_orders,
       SUM(amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY user_id
ORDER BY total_spent DESC, user_id;

Output:

 user_id | paid_orders | total_spent 
---------+-------------+-------------
       1 |           3 |      775000
       4 |           2 |      520000
       8 |           1 |      400000
       2 |           2 |      370000
       5 |           1 |      300000
       3 |           1 |      150000
       6 |           1 |      150000
(7 rows)

User 1 (Budi) has spent the most. Showing user IDs instead of names is not very friendly; in Part 6 we will JOIN the users table to display names.

Grouping by an expression

You can group by any expression, not just a column. Monthly revenue is the most common example:

SELECT to_char(ordered_at, 'YYYY-MM') AS month,
       COUNT(*)    AS paid_orders,
       SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY month
ORDER BY month;

Output:

  month  | paid_orders | revenue 
---------+-------------+---------
 2025-07 |           5 |  945000
 2025-08 |           3 |  820000
 2025-09 |           3 |  900000
(3 rows)

Notice we used the alias month in GROUP BY. PostgreSQL allows output column aliases in GROUP BY and ORDER BY, but not in WHERE or HAVING. Another common approach is date_trunc('month', ordered_at), which keeps the value as a timestamp.

HAVING: filtering groups

WHERE filters rows before grouping. HAVING filters groups after aggregation. To list only customers who spent at least Rp400,000:

SELECT user_id,
       COUNT(*)    AS paid_orders,
       SUM(amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING SUM(amount) >= 400000
ORDER BY total_spent DESC, user_id;

Output:

 user_id | paid_orders | total_spent 
---------+-------------+-------------
       1 |           3 |      775000
       4 |           2 |      520000
       8 |           1 |      400000
(3 rows)

Think of the order of execution like this:

1. FROM: pick the table
2. WHERE: drop rows (here: non-paid orders)
3. GROUP BY: build groups
4. HAVING: drop groups
5. SELECT: compute output columns
6. ORDER BY / LIMIT: sort and cut

If you try to use an aggregate in WHERE, PostgreSQL stops you, because groups do not exist yet at that step:

SELECT user_id, COUNT(*)
FROM orders
WHERE COUNT(*) > 1
GROUP BY user_id;

Output:

ERROR:  aggregate functions are not allowed in WHERE
LINE 3: WHERE COUNT(*) > 1
              ^

Conditional aggregates with FILTER

Want several counts in one row, like a dashboard summary? PostgreSQL supports the SQL-standard FILTER clause, which is much cleaner than SUM(CASE WHEN ... THEN 1 ELSE 0 END):

SELECT COUNT(*) FILTER (WHERE status = 'paid')     AS paid,
       COUNT(*) FILTER (WHERE status = 'pending')  AS pending,
       COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
       SUM(amount) FILTER (WHERE status = 'paid')  AS paid_revenue
FROM orders;

Output:

 paid | pending | refunded | paid_revenue 
------+---------+----------+--------------
   11 |       2 |        1 |      2665000
(1 row)

COUNT(DISTINCT ...)

How many different people bought something, and how many different courses were sold?

SELECT COUNT(DISTINCT user_id)   AS buyers,
       COUNT(DISTINCT course_id) AS courses_sold
FROM orders;

Output:

 buyers | courses_sold 
--------+--------------
      7 |            7
(1 row)

Seven of our eight users have at least one order (Agus has none), and seven of the eight courses were ordered (the Docker draft was not).

Common mistakes

• Selecting a column that is not grouped. Every column in SELECT must either be in GROUP BY or inside an aggregate. Otherwise PostgreSQL cannot know which value to show:

SELECT user_id, status, COUNT(*)
FROM orders
GROUP BY user_id;

Output:

ERROR:  column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT user_id, status, COUNT(*)
                        ^

• Using WHERE instead of HAVING (or vice versa). Row conditions go in WHERE, which is also faster because fewer rows get grouped. Aggregate conditions go in HAVING.
• Forgetting that SUM of zero rows is NULL, not 0. Use COALESCE(SUM(amount), 0) when you need a number.
• Counting the wrong thing. COUNT(city) silently skips NULL values. Use COUNT(*) to count rows.
• Averaging integers and expecting an integer. AVG returns NUMERIC; round it explicitly.

Exercise

1. Count how many users live in each city, including a group for NULL. Sort by count descending.
2. For each course_id, show the number of paid orders and total revenue. Keep only courses with at least 2 paid orders. (You should get courses 1, 3 and 6.)
3. Using FILTER, show total revenue for July, August and September 2025 in a single row.

Summary

You learned the main aggregate functions, the difference between COUNT(*) and COUNT(column), how GROUP BY builds groups, how HAVING filters them, and how FILTER and COUNT(DISTINCT) make reports concise. Our platform has made Rp2,665,000 in paid revenue so far.

So far we have only looked at one table at a time. In Part 6 we connect users, courses and orders with foreign keys and combine them with JOINs.