Skip to content
Advertisement

SQL conditions with COUNT in select query

In the following SQL query, I want to display if factor count is greater than one select NULL else return factor.

SELECT 
    CASE 
        WHEN COUNT(factor) > 1 
            THEN NULL
        ELSE factor
    END
FROM
    TABLE 
WHERE 
    ColumnA = '1'
    AND ColumnB = '2';

However, I get this error:

enter image description here

EDITED

It is working fine if we use it as follows, however, I need to use factor in else part and Null in IF part

SELECT 
    CASE 
       WHEN COUNT(factor) > 1 
           THEN 'A'
           ELSE 'B'
    END
FROM 
    TABLE 
WHERE 
    ColumnA = '1'
    AND ColumnB = '2';

Advertisement

Answer

You can’t use COUNT(factor) and factor in one expression, try an aggregation, mox(factor) or min(factor) should be the same…

SELECT 
   CASE WHEN COUNT(factor) > 1 
   THEN 
      NULL
   ELSE 
      MAX(factor)
   END
FROM TABLE 
WHERE ColumnA = '1'
AND ColumnB = '2';

But the error message doesn’t fit, it should be

ORA-00937: not a single-group group function

db-fiddle

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement