Count Rows Per Group With GROUP BY

"How many orders for each status?" "How many customers in each city?" These are the same question: count the rows, one number per group. GROUP BY does it in one query. This page walks through the shape, the sorting trick everyone wants, and the filter on the count.

Step 1: a small table

CREATE TABLE orders (
  order_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL,
  status      text NOT NULL
);
INSERT INTO orders (customer_id, status) VALUES
  (1, 'shipped'), (1, 'paid'), (2, 'shipped'), (3, 'cancelled'),
  (3, 'shipped'), (3, 'paid'), (5, 'shipped'), (5, 'new'),
  (6, 'paid'), (2, 'new'), (1, 'new'), (8, 'paid');

CREATE TABLE
INSERT 0 12

Step 2: count per status

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

  status   | orders
-----------+--------
 cancelled |      1
 new       |      3
 paid      |      4
 shipped   |      4
(4 rows)

Every column in SELECT must either be in GROUP BY or be inside an aggregate like count(*). status is in GROUP BY, and count(*) is the aggregate. If you add another column that is in neither, PostgreSQL stops with "must appear in the GROUP BY clause or be used in an aggregate function".

Step 3: biggest group first

Sort by the count to see which group is largest. Give the count a name and sort by it, or repeat the expression.

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

  status   | orders
-----------+--------
 paid      |      4
 shipped   |      4
 new       |      3
 cancelled |      1
(4 rows)

ORDER BY orders DESC works because ORDER BY runs after the SELECT list, so the alias exists. The second sort key, status, breaks ties so the order is the same every time.

Step 4: group by two columns

List two columns to get one row per combination.

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

 customer_id |  status   | orders
-------------+-----------+--------
           1 | new       |      1
           1 | paid      |      1
           1 | shipped   |      1
           2 | new       |      1
           2 | shipped   |      1
           3 | cancelled |      1
(6 rows)

Customer 1 appears three times, once for each status they have. Group by the finest detail you need and no finer. A grouping on a unique column such as order_id gives one group per row, and every count is 1.

Step 5: keep only the big groups

A filter on the count goes in HAVING, because the count does not exist yet when WHERE runs.

SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
HAVING count(*) >= 3
ORDER BY customer_id;

 customer_id | orders
-------------+--------
           1 |      3
           3 |      3
(2 rows)

Use WHERE for conditions on single rows, like WHERE status <> 'cancelled', and HAVING for conditions on the group's result.

Related course: SQL for Beginners.