SQL Server -Misconception about Count(ColumnName) vs Count(1) vs Count(*)

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.

But here 1 does NOT refer to an ordinal position of a column. This will not count the records of the first column of the table as COUNT(ColumnName) does.

 

SELECT Count(*) AS [Count(*)]            -- 5 Count
	,COUNT(1) AS [Count(1)]          -- 5 Count
	,COUNT('Test') AS [Count(Test)]   -- 5 Count
FROM @Products

Count_diffMethod

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.

WithoutCount

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

CountColumn

That’s it guys. Hope you enjoyed this post. Please leave your comment. Thank you.