The Microsoft Excel function `countif`

counts cells that satisfy a condition:

**Excel:** =COUNTIF(<source>, **<condition>**)

The same behavior can be obtained in SQL by using a `case`

expression inside the `count`

function:

** SQL:** COUNT(CASE WHEN **<condition>** THEN 1 END)

In Excel, the <source> defines arbitrary cells—Ax:Ay in the following examples. In SQL, the picking the rows is separate from the picking of the columns. The the `group by`

and `over`

clauses specify the rows. The column is explicitly used in the `<condition>`

that is put into the `case`

expression.

**Excel:** =COUNTIF(**A**x:**A**y, **42**)
** SQL:** COUNT(CASE WHEN **A = 42** THEN 1 END)

The condition is not put under quotes—not even when using a comparison operator:

**Excel:** =COUNTIF(Ax:Ay, **"> 42"**)
** SQL:** COUNT(CASE WHEN **A > 42** THEN 1 END)

Text values, however, must be put under single quotes0:

**Excel:** =COUNTIF(**A**x:Ay, **"Marvin"**)
** SQL:** COUNT(CASE WHEN A = **'Marvin'** THEN 1 END)

Whether or not SQL text comparisons ignore case differences depends on the so-called collation. Even the default varies between database products: MySQL, MariaDB and SQL Server perform case-insensitive comparisons by default. PostgreSQL, the Oracle database and SQLite do—per default—take case differences into account.

Unlike the Excel `countif`

function, SQL does not apply wildcard matches when comparing strings with the equals sign (`=`

). To use wildcards in SQL, you have to use the `like`

operator. The `like`

operator uses underscore (`_`

) as a wildcard for a single character and the percent sign (`%`

) as the wildcard for any number of characters—like `?`

and `*`

in Excels `countif`

.

**Excel:** =COUNTIF(Ax:Ay, **"Marvin***")
** SQL:** COUNT(CASE WHEN A **LIKE 'Marvin%'** THEN 1 END)

`Countif`

over multiple columns is done as the sum of one `count`

function per column:

**Excel:** =COUNTIF(**A**x:**C**y, 42)
** SQL:** COUNT(CASE WHEN **A** = 42 THEN 1 END) +
COUNT(CASE WHEN **B** = 42 THEN 1 END) +
COUNT(CASE WHEN **C** = 42 THEN 1 END)

The function `Countifs`

can often be implemented with an `and`

condition in the `case`

expression.

**Excel:** =COUNTIFS(Ax:Ay, 42, Bx:By, 43)
** SQL:** COUNT(CASE WHEN A = 42 AND B = 43 THEN 1 END)

The function `counta`

can be implemented with a `case`

expression as well. For that, SQL makes a distinction between empty strings and the `null`

value. The following expression counts the rows that have neither the `null`

value or the empty string.

**SQL:** COUNT(CASE WHEN **A IS NOT NULL AND A != ''** THEN 1 END)

Note that the SQL equals operator (`=`

) cannot be used to check for the null value—you have to use `is [not] null`

instead.

More about this and related topics:

The SQL

`case`

expressionThe

`filter`

clause in SQLThe SQL

`null`

value