Excels
SUMIF
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 quotes 0 :
Excel: =SUMIF(Ax:Ay, "Marvin", Bx:By)