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

sql sql-server count aggregate-functions select-case
Panzercrisis picture Panzercrisis · Oct 30, 2012 · Viewed 43k times · Source

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!

Answer

D'Arcy Rittich picture D'Arcy Rittich · Oct 30, 2012

Question: return 1 when there's one or more rows in the table, 0 otherwise:

In this case, there is no need for COUNT. Instead, use EXISTS, which rather than counting all records will return as soon as any is found, which performs much better:

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

Related questions

Fastest way to determine if record exists

As the title suggests... I'm trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not. Sample query: SELECT COUNT(*) FROM products WHERE products.id = ?; vs SELECT COUNT(products.…

Read More
Select COUNT(*) of subquery without running it twice

I've got a procedure to return a result set which is limited by page number and some other stuff. As an OUTPUT parameter I need to return a total amount of selected rows according to the parameters except the page …

Read More
SQL COUNT overflow

Here is my query: SELECT COUNT(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0 Here is the result: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int. The table has 4 billion rows. I don't except this …

Read More