How best to Count(*) with a CASE STATEMENT?

Question

The following SQL (on SQL Server) returns an error of:

Incorrect syntax near '*'

Is there something inherently wrong with using the following SELECT statement?:

SELECT
COUNT(CASE WHEN <conditions> THEN * ELSE NULL END) as conditionalcountall
FROM TABLE

I tried this variation which also failed:

SELECT
CASE WHEN <conditions> THEN COUNT(*) ELSE NULL END as conditionalcountall
FROM TABLE

Solution 1

I tend to like sum()

SELECT
SUM(CASE WHEN <conditions> THEN 1 ELSE 0 END) as conditionalcountall
FROM TABLE

Solution 2

Try This, it is Tested

SELECT
CASE WHEN 1 = 1 THEN COUNT(*) ELSE NULL END as conditionalcountall
FROM TABLE

1 = 1is example conditions

Demo:-

Create table #temp (id int , col1 varchar (10))
go

insert into #temp values (1 , 'aaaa')
insert into #temp values (2 , 'bbbb')
insert into #temp values (3 , 'cccc')

SELECT
CASE WHEN 1 = 1 THEN COUNT(*) ELSE NULL END as conditionalcountall
FROM #temp

Result:

In Case Condation like that id = 1 you should select Count(*) in CASE cluse in your query

like this:

SELECT
CASE WHEN id = 1 THEN (select COUNT(*) from  #temp) ELSE NULL END as conditionalcountall
FROM #temp

Result:-

Note: if You used Count(*) directly, you counted the id column, so you should use group by as next:

SELECT
CASE WHEN id = 1 THEN  COUNT(*) ELSE NULL END as conditionalcountall
FROM #temp
group by id

Result:

Comments

[Roman Marusyk]

This is a great place to start: How to post a T-SQL question on a public forum.

[procpy]

Thanks Ahmed but see original post, I tried that originally and it failed.

[ahmed abdelqader]

@ProcComment what is the error that you get, plz add an example of real data.

[Laughing Vergil]

This is the standard way to approach this type of query. In most SQL dialects, you can leave out the 'ELSE 0' section, but there is no good reason to do so.

[John Cappelletti]

@LaughingVergil Valid point regarding Else 0. For me if not a muscle-memory thing, it is just more readable and you won't get a NULL value if the condition is never met.

[ahmed abdelqader]

@ProcComment the answer has been updated via adding a demo.

[procpy]

it seems you are right. I tested some very simple logic using this method on using my database and it returned a result. Something else in my "real" SELECT/CASE STATEMENT must be causing the issue. I marked the other response as the answer because it has fixed the problem. Thank you for the demo, it's good to see this indeed works.

[StuKay]

From SQL 2012 onwards you could use the IIF statement instead of the CASE statement. E.g. SUM(IIF(<Conditions>, 1, 0)

[John Cappelletti]

@StuKay Yes very true, but there is no performance gain, only eye candy :)