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

Answer 1

I tend to like sum()

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

Answer 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: