Aggregation, GROUP BY, and HAVING

An aggregate function turns many rows into one value. GROUP BY splits the rows into groups first, so you get one value per group.

Function	Result
`count(*)`	number of rows
`count(col)`	number of non-`NULL` values
`count(DISTINCT col)`	number of distinct non-`NULL` values
`sum(col)`, `avg(col)`	total and mean, ignoring `NULL`
`min(col)`, `max(col)`	smallest and largest

Example 1: aggregate the whole table

Without GROUP BY, the whole table is one group.

SQL

SELECT count(*) AS employees,
       sum(salary) AS payroll,
       round(avg(salary), 2) AS average,
       min(salary) AS lowest,
       max(salary) AS highest
FROM employees;

Output

 employees | payroll |  average  | lowest | highest 
-----------+---------+-----------+--------+---------
        12 | 1540000 | 128333.33 |  70000 |  250000
(1 row)

Example 2: GROUP BY

SQL

SELECT dept_id, count(*) AS headcount, sum(salary) AS payroll
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

Output

 dept_id | headcount | payroll 
---------+-----------+---------
       1 |         5 |  840000
       2 |         3 |  335000
       3 |         2 |  200000
       4 |         1 |   95000
         |         1 |   70000
(5 rows)

Example 3: every non-aggregated column must be grouped

SQL

SELECT dept_id, name, count(*)
FROM employees
GROUP BY dept_id;

Output

ERROR:  column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT dept_id, name, count(*)
                        ^

The database cannot pick one name for a group of five. Either add name to GROUP BY, or aggregate it.

SQL

SELECT dept_id, string_agg(name, ', ' ORDER BY name) AS people
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

Output

 dept_id |             people              
---------+---------------------------------
       1 | Arjun, Kiran, Meera, Neha, Ravi
       2 | Divya, Pooja, Sameer
       3 | Anita, Vikram
       4 | Rohan
         | Tara
(5 rows)

Example 4: group by several columns

SQL

SELECT customer_id, status, count(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;

Output

 customer_id |  status   | orders 
-------------+-----------+--------
           1 | delivered |      2
           1 | shipped   |      1
           2 | cancelled |      1
           2 | delivered |      1
           3 | delivered |      2
           4 | placed    |      1
           5 | placed    |      1
(7 rows)

Example 5: WHERE filters rows, HAVING filters groups

WHERE runs before grouping, so it cannot see aggregates. HAVING runs after.

SQL

SELECT dept_id, count(*) AS headcount
FROM employees
WHERE salary > 80000
GROUP BY dept_id
HAVING count(*) >= 2
ORDER BY dept_id;

Output

 dept_id | headcount 
---------+-----------
       1 |         5
       2 |         3
(2 rows)

SQL

SELECT dept_id, count(*)
FROM employees
WHERE count(*) >= 2
GROUP BY dept_id;

Output

ERROR:  aggregate functions are not allowed in WHERE
LINE 3: WHERE count(*) >= 2
              ^

Put conditions on plain columns in WHERE. It removes rows earlier, so less data is grouped.

Example 6: count(*) vs count(column) vs count(DISTINCT)

SQL

SELECT count(*) AS rows,
       count(dept_id) AS non_null_dept,
       count(DISTINCT dept_id) AS distinct_depts
FROM employees;

Output

 rows | non_null_dept | distinct_depts 
------+---------------+----------------
   12 |            11 |              4
(1 row)

Example 7: conditional aggregation with FILTER

FILTER (PostgreSQL, SQLite) counts only rows that match. Other databases use sum(CASE WHEN ... THEN 1 ELSE 0 END).

SQL

SELECT customer_id,
       count(*) AS total,
       count(*) FILTER (WHERE status = 'delivered') AS delivered,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Output

 customer_id | total | delivered | cancelled 
-------------+-------+-----------+-----------
           1 |     3 |         2 |         0
           2 |     2 |         1 |         1
           3 |     2 |         2 |         0
           4 |     1 |         0 |         0
           5 |     1 |         0 |         0
(5 rows)

SQL

SELECT customer_id,
       sum(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Output

 customer_id | delivered 
-------------+-----------
           1 |         2
           2 |         1
           3 |         2
           4 |         0
           5 |         0
(5 rows)

Example 8: pivot rows into columns

Conditional aggregation is the standard way to pivot.

SQL

SELECT d.name AS department,
       count(*) FILTER (WHERE e.salary < 100000) AS under_100k,
       count(*) FILTER (WHERE e.salary >= 100000) AS over_100k
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
ORDER BY d.name;

Output

 department  | under_100k | over_100k 
-------------+------------+-----------
 Engineering |          0 |         5
 HR          |          1 |         0
 Marketing   |          1 |         1
 Sales       |          2 |         1
(4 rows)

Example 9: aggregate over a join

Total value of each order: multiply price by quantity, then sum per order.

SQL

SELECT o.id AS order_id, sum(p.price * oi.quantity) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.id
ORDER BY o.id;

Output

 order_id | total  
----------+--------
        1 | 950.00
        2 |  50.00
        3 | 460.00
        4 |  75.00
        5 | 900.00
        6 |  75.00
        7 | 200.00
        8 | 170.00
        9 | 125.00
(9 rows)

Example 10: revenue per category, largest first

SQL

SELECT p.category, sum(p.price * oi.quantity) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY revenue DESC;

Output

  category   | revenue 
-------------+---------
 Computers   | 1800.00
 Displays    |  600.00
 Accessories |  530.00
(3 rows)

Example 11: ROLLUP adds subtotals

ROLLUP adds a subtotal row per group and a grand total. NULL in a grouping column marks a subtotal row.

SQL

SELECT status, customer_id, count(*) AS orders
FROM orders
WHERE customer_id IN (1, 2)
GROUP BY ROLLUP (status, customer_id)
ORDER BY status, customer_id;

Output

  status   | customer_id | orders 
-----------+-------------+--------
 cancelled |           2 |      1
 cancelled |             |      1
 delivered |           1 |      2
 delivered |           2 |      1
 delivered |             |      3
 shipped   |           1 |      1
 shipped   |             |      1
           |             |      5
(8 rows)

Example 12: a join before an aggregate can inflate the result

Joining orders to order_items repeats each order once per item. Counting orders after the join over-counts.

SQL

SELECT count(*) AS wrong, count(DISTINCT o.id) AS right
FROM orders o
JOIN order_items oi ON oi.order_id = o.id;

Output

 wrong | right 
-------+-------
    13 |     9
(1 row)

Summary

WHERE filters rows, HAVING filters groups.
Every selected column is either in GROUP BY or inside an aggregate.
count(*) counts rows, count(col) skips NULL.
After a join, check whether rows were repeated before you count or sum.