A Complete Guide To Count(*) vs Count(1) in SQL

SQL Server’s COUNT(*), COUNT(1), and COUNT(column_name) differ in what ways? Discover all about it here.

Count(*) vs Count(1) in SQL

In SQL Server, the SQL COUNT() method counts the rows and takes just one input. Though it is really a basic ability, nonetheless it causes uncertainty with different argument values. T-SQL codes utilizing COUNT(*) or COUNT(1) or COUNT(Column_name) or COUNT(DISTinct(Column_name) could be shown, for instance.

I have the following [Emp] table for the demonstrative use.

Create table Emp
(
               ID int identity(1,1) primary key,
               [FirstName] varchar(20),
               [MiddleName] varchar(20),
               [LastName] varchar(20)
)
go
Insert into Emp([FirstName],[MiddleName],[LastName]) values ('Rajendra',NULL,'Gupta')
Insert into Emp([FirstName],[MiddleName],[LastName]) values ('Mohan','K','Sharma')
Insert into Emp([FirstName],[MiddleName],[LastName]) values ('Johan','L','Cruyff')
Insert into Emp([FirstName],[MiddleName],[LastName]) values ('Shyam','Sunder','Agarwal')
Insert into Emp([FirstName],[MiddleName],[LastName]) values ('Ranjith','M','Eswaran')
go          
select * from Emp

All employees except row 1, per the records, have a middle name. Check record count with several arguments with the SQL COUNT tool.

COUNT(*)

Including the NULLs, the COUNT(*) returns a table’s overall row count. My Emp table shows the overall records in that table with a return of five.

Is it possible to designate another number in the COUNT() function? Indeed, the outcome will always show the overall count of rows as seen below. The COUNT() counts the rows and substitutes a designated value for the query result set, therefore producing the identical output.

COUNT(Column_name)

Should we indicate a column name in the SQL COUNT function input, the function counts the total number of rows in the table except the NULL in the designated column.

Let’s use COUNT() function with [FirstName] and [middleName] column, for instance.

Select Count([FirstName]) as ColumnwithoutNULL from Emp
Select Count([MiddleName]) as ColumnwithNULL from Emp

The first count method produces 5 while the second yields 4 since it removes NULL value present in [MiddleName] column.

EndNote

The SQL COUNT(*) generates totals of every row — including NULLs.
Counts all the rows, including NULLs, COUNT(1).
COUNT(column_name) counts all the rows without regard to NULL in the designated column.

FAQs

What is the difference between count (*) and count (*) over?

Because it lets us obtain the number of rows for every particular value of a column, the combination of COUNT() and OVER(PARTITION BY) is more potent than the COUNT() method by itself.

What does count(*) do in SQL?

We may find the table’s rows by means of the COUNT(*) clause.

What is the difference between SUM 1 and count 1 in SQL?

Rarely seen is SUM(1), since adding a 1 for every row is merely an obfuscated approach of counting the rows. Count the rows corresponding to a date. On the other hand compiles all the single counts for every date.

What does count 1 mean?

ONE: Count The COUNT(1) function substitutes value 1 for all query result set entries. Should you have NULL values, 1 also replaces them. COUNT(1) so also provides the table’s overall record count — including NULLs.