Menu
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
743
The function counta can be implemented with a case expression as well. For that, SQL makes a distinction between empty strings and the null value. The following expression counts the rows that have neither the null value or the empty string.
COUNT() with HAVINGThe HAVING clause with SQL COUNT() function can be used to set a condition with the select statement. The HAVING clause is used instead of WHERE clause with SQL COUNT() function.
You can count multiple COUNT() for multiple conditions in a single query using GROUP BY. SELECT yourColumnName,COUNT(*) from yourTableName group by yourColumnName; To understand the above syntax, let us first create a table. The query to create a table is as follows.
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
