SQL Server count case

Question

I have table in SQL Server with values for example :

1
2
2
2
1
2

I want to count how many times there is 1 value, so result of query from my example should be 2

I try

count (case status_d when 1 then 1 end) as COUNT_STATUS_1 

but the result is 4, not 2

Answer 1

SELECT COUNT(*) 
FROM YourTable
WHERE status_d = 1

Answer 2

You can achieve this by using a WHERE clause.

SELECT COUNT(*) As Total_Ones
FROM TABLE_NAME
WHERE ColumnName = 1

Or you can use the case statement as well

SELECT COUNT(CASE WHEN ColumnName = 1 THEN 1 ELSE NULL END) As Total_Ones
FROM TABLE_NAME