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

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?

CodePudding user response：

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

CodePudding user response：

You can try to use HAVING because you are judged by an aggregate function that can't use alias in where otherwise you can try to use a subquery.

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
HAVING 
  SUM(CASE WHEN column1 LIKE 'D%' THEN 1 ELSE 0 END)   
  SUM(CASE WHEN column2 LIKE 'D%' THEN 1 ELSE 0 END)   
  SUM(CASE WHEN column3 LIKE 'D%' THEN 1 ELSE 0 END) = column4