SQL COUNTIF: How to Count Rows That Match a Condition

TL;DR: Standard SQL has no COUNTIF function. Write COUNT(CASE WHEN condition THEN 1 END), which works in every database, or use the shorter dialect forms: COUNT(*) FILTER (WHERE condition) in PostgreSQL and SQLite, SUM(condition) in MySQL, and native COUNTIF() in BigQuery or COUNT_IF() in Snowflake, Databricks and DuckDB.

SQL COUNTIF is the SQL equivalent of Excel's COUNTIF: counting only the rows that meet a condition, usually next to other counts in the same query. The portable answer is COUNT(CASE WHEN status = 'paid' THEN 1 END): the CASE returns 1 for matching rows and NULL for the rest, and COUNT() skips NULLs, so you get the number of matches. Unlike a WHERE clause, this lets you count several conditions side by side in one pass, for example paid, refunded and pending orders per month in a single row.

Does SQL have a COUNTIF function?

SQL has no COUNTIF in the ISO standard, and PostgreSQL, MySQL, SQL Server, Oracle and SQLite do not ship one. Three cloud and analytics engines do: BigQuery has `COUNTIF(expression)`, Snowflake has `COUNT_IF(condition)`, and Spark SQL, Databricks and DuckDB have count_if(expr). Everywhere else you build it from COUNT plus CASE, a FILTER clause or a boolean sum.

What you need

• A table to count. The examples use an orders table with id, customer_id, status, amount and created_at.
• Any SQL client. Version notes: the FILTER clause needs PostgreSQL 9.4 or later, or SQLite 3.30.0 or later; IIF needs SQL Server 2012 or later.

How do I count rows matching a condition?

Follow these steps; each one is a complete, runnable query.

1. Count one condition with WHERE when it is the only number you need. This is the simplest and fastest form because the database can use an index on status.

SELECT COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid';

1. Switch to COUNT(CASE) when you need the conditional count next to other columns. WHERE filters the whole query, so it cannot give you paid orders and total orders in one row. CASE can:

SELECT
  COUNT(*)                                     AS total_orders,
  COUNT(CASE WHEN status = 'paid' THEN 1 END)  AS paid_orders
FROM orders;

The CASE has no ELSE, so non-matching rows return NULL and COUNT ignores them. Do not write ELSE 0: COUNT counts zeros, and you get the total again.

1. Use the shorter form your database supports. These return the same number as step 2:

-- PostgreSQL 9.4+ and SQLite 3.30+
SELECT COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders
FROM orders;

-- MySQL and MariaDB: a comparison evaluates to 1 or 0
SELECT SUM(status = 'paid') AS paid_orders
FROM orders;

-- SQL Server 2012+
SELECT SUM(IIF(status = 'paid', 1, 0)) AS paid_orders
FROM orders;

-- BigQuery
SELECT COUNTIF(status = 'paid') AS paid_orders
FROM orders;

-- Snowflake, Databricks, DuckDB
SELECT COUNT_IF(status = 'paid') AS paid_orders
FROM orders;

1. Count multiple conditions in one query (the SQL version of Excel's COUNTIFS). Combine conditions inside one CASE with AND, or add one column per condition:

SELECT
  COUNT(CASE WHEN status = 'paid' AND amount >= 100 THEN 1 END) AS paid_over_100,
  COUNT(CASE WHEN status = 'paid' THEN 1 END)                  AS paid,
  COUNT(CASE WHEN status = 'refunded' THEN 1 END)              AS refunded,
  COUNT(CASE WHEN status IN ('pending', 'failed') THEN 1 END)  AS open_or_failed
FROM orders;

1. Group it to get a conditional count per customer, day or month. This is where COUNTIF-style counts earn their keep: one row per group, several counts per row.

-- PostgreSQL: paid vs refunded orders per month
SELECT
  date_trunc('month', created_at)              AS month,
  COUNT(*)                                     AS orders,
  COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
  COUNT(*) FILTER (WHERE status = 'refunded')  AS refunded
FROM orders
GROUP BY 1
ORDER BY 1;

1. Turn the count into a rate. Divide the conditional count by the total, casting to avoid integer division:

SELECT
  ROUND(100.0 * COUNT(CASE WHEN status = 'refunded' THEN 1 END) / NULLIF(COUNT(*), 0), 1)
    AS refund_rate_pct
FROM orders;

NULLIF(COUNT(*), 0) returns NULL instead of a division-by-zero error on an empty table.

1. Filter groups by a conditional count with HAVING. For example, customers with three or more refunds:

SELECT customer_id,
       COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunds
FROM orders
GROUP BY customer_id
HAVING COUNT(CASE WHEN status = 'refunded' THEN 1 END) >= 3;

How to verify the count is right

Cross-check the conditional count against a plain WHERE count. They must match:

SELECT
  (SELECT COUNT(*) FROM orders WHERE status = 'paid')            AS via_where,
  (SELECT COUNT(CASE WHEN status = 'paid' THEN 1 END) FROM orders) AS via_case;

If they differ, the usual cause is an ELSE 0 in the CASE, or NULLs in the column (see the next section).

How does COUNTIF handle NULL values?

A condition on a NULL column is neither true nor false, it is unknown, so the row is not counted. COUNT(CASE WHEN status <> 'paid' THEN 1 END) therefore skips rows where status is NULL. To include them, say so explicitly:

SELECT COUNT(CASE WHEN status <> 'paid' OR status IS NULL THEN 1 END) AS not_paid
FROM orders;

Also note COUNT(column) and COUNT(*) differ: COUNT(column) counts non-NULL values of that column, COUNT(*) counts rows (PostgreSQL aggregate docs).

Common errors and fixes

• The count equals the total row count. You wrote COUNT(CASE WHEN ... THEN 1 ELSE 0 END). COUNT counts the zeros. Drop the ELSE, or switch to SUM.
• The result is NULL instead of 0. SUM(...) over zero rows returns NULL. Use COUNT(CASE ...), or wrap it: COALESCE(SUM(status = 'paid'), 0).
• `ERROR: syntax error at or near "FILTER"` in PostgreSQL means a version older than 9.4, or the FILTER was placed after a non-aggregate. In SQL Server and MySQL FILTER is not supported at all; use CASE.
• `Incorrect syntax near '='` in SQL Server when you write SUM(status = 'paid'). T-SQL has no boolean-to-integer conversion; use SUM(IIF(status = 'paid', 1, 0)) or CASE.
• Arithmetic overflow on huge tables in SQL Server. SUM of an int overflows above 2,147,483,647; use `COUNT_BIG` with CASE, or cast to bigint.
• `COUNTIF(DISTINCT ...)` gives odd results in BigQuery. BigQuery's docs call that signature "generally not useful". For distinct matches use COUNT(DISTINCT CASE WHEN cond THEN customer_id END).

Which form should you use?

Use WHERE when you need one count. Use COUNT(CASE WHEN ... THEN 1 END) when the query must run on more than one database or will be read by people who know only standard SQL. Use FILTER in PostgreSQL when you have several conditional aggregates, since it reads more cleanly and works with any aggregate (SUM(amount) FILTER (WHERE status = 'paid')). Use the native COUNTIF/COUNT_IF on BigQuery, Snowflake, Databricks and DuckDB.

For more patterns like this, see CASE WHEN in PostgreSQL, the FILTER clause in SQL, SQL pivot tables (conditional counts are the core of a manual pivot) and our SQL cheat sheet.

If the people asking "how many paid orders did we get last month?" are not the ones who write SQL, AI for Database lets them ask that in plain English against your own database, shows the conditional-count query it ran, and can pin it to a dashboard or alert you when the refund count crosses a threshold. Try AI for Database free at aifordatabase.com.