PostgreSQL In-Depth
Module F-4·15 min read

COUNT, SUM, AVG, GROUP BY, HAVING — the mental model that confuses beginners, explained from scratch.

Written by Jatin Jain Saraf · Architect of SupraScan

F-4 — Aggregation — Summarising Your Data

Who this module is for: You completed F-3 and can filter and sort individual rows. Now you need to answer questions about groups of rows — "how many products are in each category?", "what is the average price?", "which categories have more than 5 products?". This module covers aggregation — the most conceptually tricky part of SQL for beginners.

The Problem Aggregation Solves

So far, every query has returned rows from the table — one row of output per row of input (or fewer after filtering). Aggregation is different: it collapses multiple rows into a single summary value.

Before aggregation (6 rows, 6 output rows):
  Keyboard     | peripherals | 129.99
  Mouse        | peripherals |  49.99
  Monitor Stand| accessories |  39.99
  USB-C Hub    | accessories |  64.99
  Laptop Sleeve| accessories |  24.99
  Webcam       | peripherals |  89.99
 
After aggregation (collapse to summary per category, 2 output rows):
  peripherals | 3 products | avg $89.99
  accessories | 3 products | avg $43.32

Aggregate Functions

These functions take a column of values and return a single summary value:

-- COUNT: how many rows?
SELECT COUNT(*) FROM products;               -- total rows
SELECT COUNT(description) FROM products;     -- rows where description is NOT NULL
SELECT COUNT(DISTINCT category) FROM products; -- unique values
 
-- SUM: total of a numeric column
SELECT SUM(price) FROM products;
 
-- AVG: average value
SELECT AVG(price) FROM products;
 
-- MIN / MAX: smallest or largest value
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;
 
-- ROUND: clean up decimal output
SELECT ROUND(AVG(price), 2) FROM products;  -- round to 2 decimal places

When you use an aggregate function without GROUP BY, it collapses the entire table into a single row:

SELECT
  COUNT(*)           AS total_products,
  ROUND(AVG(price), 2) AS avg_price,
  MIN(price)         AS cheapest,
  MAX(price)         AS most_expensive,
  SUM(price)         AS total_value
FROM products;
 
--  total_products | avg_price | cheapest | most_expensive | total_value
-- ----------------+-----------+----------+----------------+-------------
--               5 |     74.99 |    39.99 |         129.99 |      374.95

GROUP BY — The Mental Model

GROUP BY is where most beginners get confused. The mental model:

  1. PostgreSQL divides all your rows into groups based on the column(s) you specify
  2. Each group is collapsed into a single output row
  3. You can then apply aggregate functions to each group separately
-- "How many products are in each category?"
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;
 
--    category   | product_count
-- --------------+---------------
--  accessories  |             3
--  peripherals  |             3

Visualise what happens:

Full table:
  Keyboard      | peripherals          ─┐
  Mouse         | peripherals           ├─ GROUP 1: peripherals (3 rows)
  Webcam        | peripherals          ─┘
  Monitor Stand | accessories          ─┐
  USB-C Hub     | accessories           ├─ GROUP 2: accessories (3 rows)
  Laptop Sleeve | accessories          ─┘
 
After GROUP BY category + COUNT(*):
  peripherals  | 3
  accessories  | 3

The Golden Rule of GROUP BY

Every column in your SELECT list must either be in the GROUP BY clause OR be wrapped in an aggregate function.

-- ✅ CORRECT: category is in GROUP BY, COUNT(*) is an aggregate
SELECT category, COUNT(*) FROM products GROUP BY category;
 
-- ✅ CORRECT: category in GROUP BY, AVG is an aggregate
SELECT category, AVG(price) FROM products GROUP BY category;
 
-- ❌ ERROR: name is neither in GROUP BY nor an aggregate
SELECT category, name, COUNT(*) FROM products GROUP BY category;
-- ERROR: column "products.name" must appear in the GROUP BY clause
-- or be used in an aggregate function

This error trips up beginners constantly. The reason: if you group by category, and there are 3 rows in the "peripherals" group, which name value should PostgreSQL return? There are three of them. It cannot pick one arbitrarily — so it forces you to either group by name too (making each row unique) or use an aggregate.

Multiple columns in GROUP BY

-- "How many in-stock vs out-of-stock products per category?"
SELECT category, in_stock, COUNT(*) AS count
FROM products
GROUP BY category, in_stock
ORDER BY category, in_stock;
 
--    category   | in_stock | count
-- --------------+----------+-------
--  accessories  | f        |     1
--  accessories  | t        |     2
--  peripherals  | t        |     3

When you GROUP BY multiple columns, each unique combination of those columns becomes one output row.

Common Aggregation Patterns

-- Total value of inventory by category
SELECT
  category,
  COUNT(*) AS num_products,
  SUM(price) AS total_value,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY total_value DESC;
 
-- Cheapest and most expensive per category
SELECT
  category,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category;
 
-- Count in-stock vs out-of-stock across the whole catalog
SELECT
  in_stock,
  COUNT(*) AS count
FROM products
GROUP BY in_stock;

HAVING — Filtering Groups

WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

-- Categories with more than 2 products
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 2;
 
-- Categories where average price is above $50
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

Sign in & Register
PreviousModule F-3: Filtering, Sorting, and Finding What You NeedNext Module F-5: Connecting Tables — Joins Demystified

Discussion

0

Join the discussion

Loading comments...
PortfolioBlog

© 2026 Jatin Jain Saraf (JJS). All rights reserved.