sqlsql-servercountaggregate-functionsselect-case

How can you use COUNT() in a comparison in a SELECT CASE clause in Sql Server?

Let's say you want do something along the following lines:

SELECT CASE 
    WHEN (SELECT COUNT(id) FROM table WHERE column2 = 4) > 0
    THEN 1 ELSE 0 END

Basically just return 1 when there's one or more rows in the table, 0 otherwise. There has to be a grammatically correct way to do this. What might it be? Thanks!

Solution

  • You could do this:

    SELECT CASE WHEN COUNT(ID) >=1 THEN 1 WHEN COUNT (ID) <1 THEN 0 END FROM table WHERE Column2=4

    Reference: http://msdn.microsoft.com/en-us/library/ms181765.aspx