Case when SQL 2 conditions in sumif

Excels Sumif in SQL

The Microsoft Excel function sums the cells for which a condition is met:

In the query language SQL, this behavior can be achieved by using an expression in the function:

While in Excel you can select any cells in the - in the following example - in SQL the selection of the rows and columns is done separately. The lines are determined by the and clauses. The column must be used explicitly in the im expression.

The condition is not placed in quotation marks, even if other comparisons are used:

In the expression, different values ​​can be used in the and branches. This means that the function can also be implemented with three arguments:

Texts must be placed under single apostrophes in SQL0:

Whether a text comparison is case-sensitive or not depends on the so-called collation. The default settings also differ here: MySQL, MariaDB and SQL Server do not differentiate between uppercase and lowercase letters with the default settings. In PostgreSQL, the Oracle database and SQLite, a distinction is made between uppercase and lowercase letters by default.

Unlike the Excel function, SQL does not use wildcard characters when comparing text with the equals sign (). To use wildcard characters in SQL, one can use the operator. The underscore () serves as a placeholder for a character and the percent sign () as a placeholder for any number of characters - correspondingly and in Excel.

over several columns is implemented in SQL by the sum of one function per column:

The function can often be implemented by a -condition in the -expression.

More about how it works and other options:

About the author

Markus Winand is the SQL Renaissance Ambassador on a mission to alert developers to the evolution of SQL in the 21st century. Markus can be hired as a trainer, speaker and consultant on winand.at.

Footnotes