select sum(a), sum(b where c=1) from db; sql conditions in select statement

Question

i guess i just lack the keywords to search, but this is burning on my mind:

how can i add a condition to the sum-function in the select-statement like

select sum(a), sum(b where c=1) from db;?

this means, i want to see the sum of column a and the sum of column b, but only of the records in column b of which column c has the value 1.

the output of heidi just says "bad syntac near WHERE". may there be any other way?

thanks in advance and best regards from Berlin, joachim

Answer 1

The exact syntax may differ depending on the database engine, however it will be along the lines of

SELECT
    sum(a),
    sum(CASE WHEN c = 1 THEN b ELSE 0 END)
FROM
    db

Answer 2

select sum(case when c=1 then b else 0 end)

This technique is useful when you need a lot of aggregates on the same set of data - you can query the entire table without applying a where filter, and have a bunch of these which give you aggregated data for a specific filter.

It's also useful when you need a lot of counts based on filters - you can do sums of 1 or 0:

select sum(case when {somecondition} then 1 else 0 end)