SQL sum the number of satisfied conditions and compare with other column

Question

I want to check for three columns if a certain condition holds. Next, I want to sum these three booleans and check whether they are the same as a value in a fourth column.

SELECT SUM(CASE WHEN column1 LIKE 'D%' THEN 1 ELSE 0 END) AS bool1
       SUM(CASE WHEN column2 LIKE 'D%' THEN 1 ELSE 0 END) AS bool2
       SUM(CASE WHEN column3 LIKE 'D%' THEN 1 ELSE 0 END) AS bool3
FROM table
WHERE bool1 + bool2 + bool3 = column4

This gives an error however. How should I change the query?

Answer 1

Aggregate function SUM() operates on all values of a column, so it does not make sense to compare it to a specific value of another column.

I suspect that you want something like this:

SELECT *
FROM tablename
WHERE (column1 LIKE 'D%') + (column2 LIKE 'D%') + (column3 LIKE 'D%') = column4

Each of the boolean expressions columnX LIKE 'D%' evaluates to 0 for false and 1 for true.

If there are nulls in the columns change to:

WHERE COALESCE(column1 LIKE 'D%', 0) + 
      COALESCE(column2 LIKE 'D%', 0) + 
      COALESCE(column3 LIKE 'D%', 0) = column4