How to count rows with a condition in SQL

1SUM(CASE …) or COUNT(CASE …): works everywhereRecommended

A CASE expression turns each row into a value you can aggregate (see IF/THEN in a SELECT for how it works). There are two ways to count with it. SUM(CASE WHEN … THEN 1 ELSE 0 END) adds a 1 for every matching row. COUNT(CASE WHEN … THEN 1 END) relies on CASE returning NULL when no branch matches, and COUNT skipping NULLs. Either one is a good default: standard SQL that PostgreSQL, MySQL, SQL Server, SQLite and Oracle all accept, so the query keeps working if it moves to another database.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    status   TEXT,
    amount   INTEGER,
    coupon   TEXT
);
INSERT INTO orders VALUES
    (1, 'ana', 'paid',     40, NULL),
    (2, 'ana', 'paid',     15, 'SAVE10'),
    (3, 'ben', 'refunded', 25, NULL),
    (4, 'ben', 'paid',     60, 'SAVE10'),
    (5, 'cy',  'pending',  30, NULL),
    (6, 'cy',  'paid',     20, 'FREESHIP');

-- One pass over the table, one count per condition
SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
    COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded,
    SUM(CASE WHEN amount >= 30 THEN 1 ELSE 0 END) AS big
FROM orders;

-- COUNT skips NULLs, not zeros: ELSE 0 makes it count every row
SELECT
    COUNT(CASE WHEN status = 'paid' THEN 1 END) AS right_way,
    COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS wrong_way
FROM orders;

-- No rows to aggregate: SUM gives NULL, COUNT gives 0
SELECT
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS sum_form,
    COALESCE(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), 0) AS fixed,
    COUNT(CASE WHEN status = 'paid' THEN 1 END) AS count_form
FROM orders
WHERE customer = 'zoe';

The first query reads the table once and returns 6 orders, 4 paid, 1 refunded and 3 of 30 or more. The second shows the one mistake to avoid: with ELSE 0 inside COUNT, every row produces a non-NULL value, so it counts all 6 instead of 4. Use ELSE 0 with SUM and no ELSE with COUNT. The last query has no rows to aggregate: SUM returns NULL there while COUNT returns 0, so wrap the SUM form in COALESCE(…, 0) when a filter can leave nothing behind. With GROUP BY every group has at least one row, so the difference doesn't show up there.

2COUNT(*) FILTER (WHERE …): the standard shorthand

The SQL standard has a clause made for this: write the aggregate, then FILTER (WHERE condition), and only the rows that pass the condition reach it. It reads closer to what you mean than a CASE, and it works with any aggregate, so SUM(amount) FILTER (WHERE …) adds up only the matching amounts. PostgreSQL has it since 9.4 and SQLite since 3.30. MySQL and SQL Server don't support it, which is why the CASE form is the safer default.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    status   TEXT,
    amount   INTEGER,
    coupon   TEXT
);
INSERT INTO orders VALUES
    (1, 'ana', 'paid',     40, NULL),
    (2, 'ana', 'paid',     15, 'SAVE10'),
    (3, 'ben', 'refunded', 25, NULL),
    (4, 'ben', 'paid',     60, 'SAVE10'),
    (5, 'cy',  'pending',  30, NULL),
    (6, 'cy',  'paid',     20, 'FREESHIP');

-- SQL standard; SQLite 3.30+ and PostgreSQL 9.4+
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid,
    COUNT(*) FILTER (WHERE coupon IS NOT NULL) AS coupon,
    SUM(amount) FILTER (WHERE status = 'paid') AS paid_sum
FROM orders;

The result is 6 orders, 4 paid, 3 with a coupon and 135 as the sum of the paid amounts (40 + 15 + 60 + 20). The paid count matches the CASE version in section 1 exactly; FILTER is a shorter way to say the same thing. Like COUNT(CASE …), COUNT(*) FILTER returns 0 when nothing matches. If your database is PostgreSQL or a current SQLite and the query will stay there, use whichever reads better to you.

3Conditional counts per group with GROUP BY

Add a GROUP BY and the same expressions count inside each group, so one query gives every customer's total, paid and other orders as columns of one row. To keep only the groups that meet a condition on such a count, put the expression in HAVING, which filters after grouping (WHERE runs before it and can't see aggregates).

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    status   TEXT,
    amount   INTEGER,
    coupon   TEXT
);
INSERT INTO orders VALUES
    (1, 'ana', 'paid',     40, NULL),
    (2, 'ana', 'paid',     15, 'SAVE10'),
    (3, 'ben', 'refunded', 25, NULL),
    (4, 'ben', 'paid',     60, 'SAVE10'),
    (5, 'cy',  'pending',  30, NULL),
    (6, 'cy',  'paid',     20, 'FREESHIP');

SELECT
    customer,
    COUNT(*) AS orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
    SUM(CASE WHEN status <> 'paid' THEN 1 ELSE 0 END) AS other
FROM orders
GROUP BY customer
ORDER BY customer;

-- Keep only groups with at least two paid orders
SELECT customer
FROM orders
GROUP BY customer
HAVING SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) >= 2;

Each customer has 2 orders: ana has both paid, ben and cy one paid and one other. The HAVING query returns only ana, the one customer with two paid orders. One thing to watch in the other column: a row whose status is NULL fails both = 'paid' and <> 'paid', so it would land in neither count. If the column allows NULL, count the rest as SUM(CASE WHEN status = 'paid' THEN 0 ELSE 1 END), which puts those rows in other.

4SUM(condition): the shortcut in SQLite and MySQL

In SQLite and MySQL a comparison such as status = 'paid' is a number: 1 when it holds, 0 when it doesn't. Summing it therefore counts the matches, with no CASE at all. It is short and common in MySQL code, but it doesn't port: PostgreSQL has a real boolean type and rejects it with function sum(boolean) does not exist, and SQL Server doesn't allow a comparison as a value at all.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    status   TEXT,
    amount   INTEGER,
    coupon   TEXT
);
INSERT INTO orders VALUES
    (1, 'ana', 'paid',     40, NULL),
    (2, 'ana', 'paid',     15, 'SAVE10'),
    (3, 'ben', 'refunded', 25, NULL),
    (4, 'ben', 'paid',     60, 'SAVE10'),
    (5, 'cy',  'pending',  30, NULL),
    (6, 'cy',  'paid',     20, 'FREESHIP');

-- A comparison is 1 or 0 in SQLite and MySQL, so SUM counts the 1s
SELECT status = 'paid' AS is_paid, amount > 25 AS big
FROM orders
WHERE id <= 3;

SELECT
    SUM(status = 'paid') AS paid,
    SUM(amount > 25) AS big,
    SUM(coupon IS NOT NULL) AS coupon
FROM orders;

The first query shows the raw values: order 1 is paid and over 25 (1, 1), order 2 is paid but only 15 (1, 0), order 3 is neither. Summed over the table that gives 4 paid, 3 over 25 and 3 with a coupon, the same numbers as the CASE form. In PostgreSQL, cast the boolean first, SUM((status = 'paid')::int), or use FILTER. If the column itself can be NULL, the comparison is NULL for that row and SUM skips it, which is what you want for a count.

5COUNT(*), COUNT(column) and COUNT(DISTINCT column)

What you put inside COUNT is itself a condition. COUNT(*) counts rows. COUNT(coupon) counts rows where coupon is not NULL, so it is a built-in "has a value" count. COUNT(DISTINCT coupon) counts the different non-NULL values. Combine DISTINCT with a CASE to count distinct values among the rows that match a condition.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    status   TEXT,
    amount   INTEGER,
    coupon   TEXT
);
INSERT INTO orders VALUES
    (1, 'ana', 'paid',     40, NULL),
    (2, 'ana', 'paid',     15, 'SAVE10'),
    (3, 'ben', 'refunded', 25, NULL),
    (4, 'ben', 'paid',     60, 'SAVE10'),
    (5, 'cy',  'pending',  30, NULL),
    (6, 'cy',  'paid',     20, 'FREESHIP');

-- COUNT(column) and COUNT(DISTINCT column) skip NULLs
SELECT
    COUNT(*) AS all_rows,
    COUNT(coupon) AS with_coupon,
    COUNT(DISTINCT coupon) AS coupons,
    COUNT(DISTINCT customer) AS buyers
FROM orders;

-- Distinct values that meet a condition
SELECT COUNT(DISTINCT CASE WHEN coupon = 'SAVE10' THEN customer END) AS save10_users
FROM orders;

-- Distinct combinations of two columns: count a DISTINCT subquery
SELECT COUNT(*) AS pairs
FROM (SELECT DISTINCT customer, status FROM orders);

Of the 6 rows, 3 have a coupon, but only 2 different coupons (SAVE10 and FREESHIP): DISTINCT collapses the repeats and NULL is never counted. There are 3 customers, and 2 of them (ana and ben) used SAVE10. The customer and status pairs come to 5, because ana's two paid orders are one pair. MySQL also accepts COUNT(DISTINCT customer, status), but SQLite rejects it with wrong number of arguments to function COUNT(), and so do PostgreSQL and SQL Server, so the subquery is the form that works everywhere.

6The percentage of rows that match

A percentage is a conditional count divided by COUNT(*). Start the arithmetic with 100.0, not 100: both counts are integers, and integer division in SQLite, PostgreSQL and SQL Server drops the fraction. ROUND(x, 1) keeps one decimal. For each group's share of all rows, divide by SUM(COUNT(*)) OVER (), a window function that adds up the group counts.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    status   TEXT,
    amount   INTEGER,
    coupon   TEXT
);
INSERT INTO orders VALUES
    (1, 'ana', 'paid',     40, NULL),
    (2, 'ana', 'paid',     15, 'SAVE10'),
    (3, 'ben', 'refunded', 25, NULL),
    (4, 'ben', 'paid',     60, 'SAVE10'),
    (5, 'cy',  'pending',  30, NULL),
    (6, 'cy',  'paid',     20, 'FREESHIP');

SELECT
    ROUND(100.0 * COUNT(CASE WHEN status = 'paid' THEN 1 END) / COUNT(*), 1) AS pct_paid,
    ROUND(100.0 * COUNT(coupon) / COUNT(*), 1) AS pct_coupon
FROM orders;

-- Each group's share of all rows: a window SUM over the counts
SELECT
    status,
    COUNT(*) AS n,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders
GROUP BY status
ORDER BY n DESC, status;

-- Integer division truncates: start with 100.0, not 100
SELECT
    COUNT(CASE WHEN status = 'paid' THEN 1 END) / COUNT(*) * 100 AS zero,
    100 * COUNT(CASE WHEN status = 'paid' THEN 1 END) / COUNT(*) AS cut
FROM orders;

Four of six orders are paid, so pct_paid is 66.7, and three of six have a coupon, 50.0. The grouped query gives each status its share: 66.7 for paid and 16.7 each for pending and refunded. The last query shows what integer math does: dividing first gives 4 / 6 = 0, so the result is 0; multiplying by 100 first gives 66, with the fraction cut off. MySQL's / always returns a decimal, so it doesn't have this problem, but 100.0 is harmless there too. In SQL Server, ROUND keeps the full decimal scale, so cast the result to DECIMAL(5,1) to show one decimal place.

7In PostgreSQL, MySQL and SQL Server

The CASE forms are the same in every database. What differs is the shorthand each one adds: FILTER in PostgreSQL, SUM(condition) and IF() in MySQL, IIF() in SQL Server. Each form below was run on PostgreSQL 18, MySQL 8.4 and SQL Server's T-SQL (via Babelfish). Oracle also takes the CASE forms.