Turn Rows Into Columns With FILTER

The shop's orders have a status: new, paid, shipped, cancelled. A grouped query gives one row per status. Sometimes you want the opposite: one row per customer, with a column for each status. That is a small pivot table, and in PostgreSQL you can build it with a plain aggregate and a FILTER clause, with no extension needed.

Step 1: the long version

One row per customer and status is easy and hard to read.

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

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

Step 2: one column per status

count(*) FILTER (WHERE ...) counts only the rows that pass the condition. Write one for each column you want.

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

 customer_id | new | paid | shipped | cancelled | total
-------------+-----+------+---------+-----------+-------
           1 |   1 |    1 |       1 |         0 |     3
           2 |   1 |    0 |       1 |         0 |     2
           3 |   0 |    1 |       1 |         1 |     3
           5 |   1 |    0 |       1 |         0 |     2
           6 |   0 |    1 |       0 |         0 |     1
           8 |   0 |    1 |       0 |         0 |     1
(6 rows)

Each row is a customer, each status is a column, and the last column checks the others add up. The numbers match the long version. Customer 1 has one order each in new, paid and shipped, so three in total, as in Step 1. A status with no orders shows 0, because count of no rows is 0.

Step 3: the same with CASE

Databases that do not have FILTER use CASE. The result is the same, and it works in every database.

SELECT customer_id,
       sum(CASE WHEN status = 'new'     THEN 1 ELSE 0 END) AS new,
       sum(CASE WHEN status = 'paid'    THEN 1 ELSE 0 END) AS paid,
       sum(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped
FROM orders
WHERE customer_id IN (1, 3)
GROUP BY customer_id
ORDER BY customer_id;

 customer_id | new | paid | shipped
-------------+-----+------+---------
           1 |   1 |    1 |       1
           3 |   0 |    1 |       1
(2 rows)

FILTER is shorter and says what it means. CASE is the portable version. Note that CASE without an ELSE gives NULL, which count skips, so count(CASE WHEN ... THEN 1 END) also works.

Step 4: sum a value, not just count

FILTER works with any aggregate. Revenue per category by month, one column per month:

SELECT p.category,
       sum(i.quantity * p.price) FILTER (WHERE o.order_date < DATE '2026-09-01')  AS august,
       sum(i.quantity * p.price) FILTER (WHERE o.order_date >= DATE '2026-09-01') AS september
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
GROUP BY p.category
ORDER BY p.category;

  category   | august | september
-------------+--------+-----------
 bags        |  44.65 |     44.65
 electronics |  23.97 |     69.49
 home        |  37.50 |     24.50
 stationery  |  20.50 |     12.50
(4 rows)

A category with no sales in a month shows NULL, and you can wrap the sum in coalesce(..., 0) if you prefer zero.

The limit of this method

The columns are fixed when you write the query. A new status, or a new month, needs a new line. If the list of columns is not known in advance, build the query in your application, or use the crosstab function from the tablefunc extension. The extension is not installed by default, and its output columns must also be declared.

Related course: SQL Intermediate.