Using condition inside COUNT() in SQL Server

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 */
FROM Sales.Invoices
WHERE TotalDryItems = 1

/* COUNT with condition inside it */
			WHEN 1
				THEN 1
FROM Sales.Invoices
Using condition inside COUNT() in SQL Server


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.