The Microsoft Excel function sumif
adds up cells that satisfy a condition:
Excel: =SUMIF(<source>, <condition>)
The same behavior can be obtained in SQL by using a case
expression inside the sum
function:
SQL: SUM(CASE WHEN <condition> THEN <wert> 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: =SUMIF(Ax:Ay, 42)
SQL: SUM(CASE WHEN A = 42 THEN A END)
The condition is not put under quotes—not even when using a comparison operator:
Excel: =SUMIF(Ax:Ay, "> 42")
SQL: SUM(CASE WHEN A > 42 THEN A END)
The case
expression accepts different values in the when
and then
branches. This allows you to do the same thing as the third argument of the sumif
function.
Excel: =SUMIF(Ax:Ay, "> 42", Bx:By)
SQL: SUM(CASE WHEN A > 42 THEN B END)
Text values, however, must be put under single quotes0:
Excel: =SUMIF(Ax:Ay, "Marvin", Bx:By)
SQL: SUM(CASE WHEN A = 'Marvin' THEN B 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 sumif
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 sumif
.
Excel: =SUMIFIF(Ax:Ay, "Marvin*", Bx:By)
SQL: suM(CASE WHEN A LIKE 'Marvin%' THEN B END)
Sumif
over multiple columns is done as the sum of one count
function per column:
Excel: =SUMIF(Ax:Cy, 42)
SQL: SUM(CASE WHEN A = 42 THEN A END) +
SUM(CASE WHEN B = 42 THEN B END) +
SUM(CASE WHEN C = 42 THEN C END)
The function Sumifs
can often be implemented with an and
condition in the case
expression.
Excel: =SUMIFS(Bx:By, Ax:Ay, 42, Cx:Cy, 43)
SQL: COUNT(CASE WHEN A = 42 AND C = 43 THEN B END)
More about this and related topics:
The SQL
case
expressionThe
filter
clause in SQLThe SQL
null
value