Excels Countif in SQL


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(Ax:Ay, 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(Ax: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(Ax:Cy, 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:

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Footnotes

  1. Depending on the configuration some databases use double quotes instead of single quotes for text values (MySQL, MariaDB).

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license