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 */
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
Using condition inside COUNT() in SQL Server

Reference

Related Articles & Tools

Leave your thoughts...

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