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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s