SQL Server Interview Question #82

What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

COUNT(*) counts all rows in the result. COUNT(column) counts rows where the specified expression is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values.

This distinction is important when nullable columns are involved. If a table contains 100 rows but 20 Email values are NULL, COUNT(*) returns 100 while COUNT(Email) returns 80.

Code Example

SELECT COUNT(*) AS TotalRows,
       COUNT(Email) AS RowsWithEmail,
       COUNT(DISTINCT Email) AS UniqueEmails
FROM dbo.Customers;