Few days ago, I was having a healthy debate with one of my colleague in my team regarding Count(columnName) vs Count(1) vs Count(*). And i observed that he was having some misunderstanding regarding Count function in sql server. So i thought it’ll be a good idea to write post regarding it.
So what’s the misunderstanding is – According to him, Count(1) will give the count of records of first column in the table and Count(2) will give the count of records of second column in the table and so on. Well really is it ? Let’s see it practically.
First let’s create the table with some sample data in it.
DECLARE @Products TABLE ( ProductID INT, ProductName VARCHAR(50), Price MONEY ) INSERT INTO @Products VALUES (1, 'iPhone 6s', 55200), (1, 'HTC ONE E9', 18000), (1, 'Samsung Galaxy J7', 15100), (1, NULL, 10200), (1, 'MOTO G3', NULL)
First let’s see the Count(*) in action.
COUNT(*) : – It counts the number of records in the table regardless of NULL values and duplicate values. Pretty simple right.
SELECT Count(*) AS [Count(*)] FROM @Products
COUNT(1) / COUNT(‘Test’) :- It counts the number of records in the table regardless of NULL values and duplicate values.
SELECT Count(*) AS [Count(*)] -- 5 Count ,COUNT(1) AS [Count(1)] -- 5 Count ,COUNT('Test') AS [Count(Test)] -- 5 Count FROM @Products
Here COUNT(1) and COUNT(‘Test’) gives you the same count because 1 or 2 or “Test” inside Count() function is a non-null expression. When you specify any non-null expression, sql server will treat them as shown in below image.
Hmmm the picture is clear now. But then what is the difference between COUNT(*) and COUNT(1) and COUNT(‘Test’) ?
Well the answer is – there is absolutely no difference between them! Alright, Let’s move ahead.
COUNT(ColumnName) :- It counts the number of records of specified column name regardless of duplicate values, But it doesn’t count the NULL values. i.e. It counts only the non-null values.
SELECT Count(ProductID) AS CountProductID ,COUNT(ProductName) AS CountProductName ,COUNT(Price) AS CountPrice FROM @Products
That’s it guys. Hope you enjoyed this post. Please leave your comment. Thank you.