You may have already used the COUNT() aggregate function in SQL Server in the form of COUNT(*), COUNT(ALL … ), COUNT(DISTINCT … ), etc… These are the general usage of COUNT function. It is also possible to use conditions in the form of CASE statement inside COUNT function. This method will come in handy when you cannot use WHERE clause in the select statement. Let’s see how to use a condition inside COUNT().
Consider a simple example to get the count of invoices from a table, with just 1 dry item. For this, you can write a SELECT query with COUNT(*) and a WHERE clause. However, You can have the same result by using a condition inside CASE() function. If you see the second SQL statement below, I have added a CASE statement with condition inside the COUNT(). This method will help you to squeeze in a condition when you could not use the same in where clause.
/* COUNT() with WHERE clause in the select query */
SELECT COUNT(*)
FROM Sales.Invoices
WHERE TotalDryItems = 1
GO
/* COUNT with condition inside it */
SELECT COUNT(CASE TotalDryItems
WHEN 1
THEN 1
ELSE NULL
END)
FROM Sales.Invoices
GO
Reference
- More about COUNT() aggregate function at Microsoft Docs.
- I’ve used WideWorldImporters sample database for the example script.