---
title: "SQL COUNTIF: How to Count Rows That Match a Condition"
description: "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(*)…"
canonical: "https://www.aifordatabase.com/blog/sql-countif/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-11-02
updated: 2026-11-02
---

# 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)\`](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions), Snowflake has [\`COUNT\_IF(condition)\`](https://docs.snowflake.com/en/sql-reference/functions/count_if), 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.

| Database | COUNTIF equivalent | Returns 0 when nothing matches? |
| --- | --- | --- |
| Any database (portable) | `COUNT(CASE WHEN cond THEN 1 END)` | Yes |
| PostgreSQL 9.4+ | `COUNT(*) FILTER (WHERE cond)` | Yes |
| SQLite 3.30+ | `COUNT(*) FILTER (WHERE cond)` | Yes |
| MySQL / MariaDB | `SUM(cond)` | No, NULL on an empty table |
| SQL Server | `SUM(IIF(cond, 1, 0))` or `COUNT(CASE ...)` | `COUNT` yes, `SUM` no |
| BigQuery | `COUNTIF(cond)` | Yes |
| Snowflake, Databricks, DuckDB | `COUNT_IF(cond)` | Yes |

## 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](https://www.postgresql.org/docs/9.4/release-9-4.html) or later, or [SQLite 3.30.0](https://www.sqlite.org/releaselog/3_30_0.html) 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.

![COUNT(CASE) returns 1 for paid rows and NULL for others, so COUNT finds 2 paid orders out of 4](https://cdn.sanity.io/images/upsr56wu/production/60bc00ab18a03e77ead508f2a3c9def41fd89e98-1344x456.png?w=1280)

FIG 01 — COUNT(...) = 2 paid orders; COUNT(\*) = 4 total.

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;
```

![Grouped result with one row per month and separate paid and refunded counts](https://cdn.sanity.io/images/upsr56wu/production/67b9a03b07f05262829c8ff9a3df66830c924127-1344x364.png?w=1280)

FIG 02 — Several COUNTIF-style counts per group, from one pass over the table.

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;
```

VIDEO · [SQL Tutorial: CASE WHEN with aggregate functions](https://www.youtube.com/watch?v=ImiEKaEBvjE) (DataCamp)

## 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](https://www.postgresql.org/docs/current/functions-aggregate.html)).

## 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\`](https://learn.microsoft.com/en-us/sql/t-sql/functions/count-big-transact-sql) 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](https://www.aifordatabase.com/blog/postgresql-case-when/), [the FILTER clause in SQL](https://www.aifordatabase.com/blog/filter-in-sql/), [SQL pivot tables](https://www.aifordatabase.com/blog/sql-pivot-table/) (conditional counts are the core of a manual pivot) and our [SQL cheat sheet](https://www.aifordatabase.com/blog/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.

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### Does SQL have COUNTIF?

Not in standard SQL, PostgreSQL, MySQL, SQL Server, Oracle or SQLite. BigQuery has COUNTIF, and Snowflake, Databricks and DuckDB have COUNT\_IF. Elsewhere, use COUNT(CASE WHEN condition THEN 1 END).

### How do I count multiple conditions in one SQL query?

Add one COUNT(CASE WHEN ... THEN 1 END) column per condition, or combine conditions with AND/OR inside one CASE. This is the SQL equivalent of Excel's COUNTIFS.

### What is the difference between COUNT(CASE) and SUM(CASE)?

COUNT(CASE WHEN cond THEN 1 END) counts non-NULL results and returns 0 when nothing matches. SUM(CASE WHEN cond THEN 1 ELSE 0 END) adds ones and zeros and returns NULL on an empty input. Both give the same count when rows exist.

### Is FILTER faster than CASE in PostgreSQL?

They are typically planned the same way and perform about the same; FILTER is mainly more readable. For a single count, a WHERE clause can be faster because it can use an index.

### How do I do COUNTIF in SQL Server?

Use COUNT(CASE WHEN condition THEN 1 END) or SUM(IIF(condition, 1, 0)). SQL Server has no COUNTIF and does not support the FILTER clause.

### How do I count distinct values with a condition?

Put the column in the CASE: COUNT(DISTINCT CASE WHEN status = 'paid' THEN customer\_id END) counts customers with at least one paid order.

Read next

*   [NORMALIZED DATABASE: 1NF, 2NF, 3NF WITH REAL ROWS →](https://www.aifordatabase.com/blog/normalized-database/)
*   [ALIASES IN SQL: COLUMN, TABLE AND SUBQUERY REFERENCE →](https://www.aifordatabase.com/blog/aliases-in-sql/)
*   [8 CALL CENTER METRICS DASHBOARD EXAMPLES (WITH SQL) →](https://www.aifordatabase.com/blog/call-center-metrics-dashboard/)

Source: https://www.aifordatabase.com/blog/sql-countif/