Aggregate Function in SQL Server

In this blog, we will learn about aggregate function in SQL Server. An aggregate function is used to calculate the given values and returns a single value.

In this blog, we will learn about aggregate function in SQL Server. An aggregate function is used to calculate the given values and returns a single value.

Types of aggregate functions in SQL Server.

1. Count
2. Avg
3. Max
4. Min
5. Sum
6. Count_Big
7. Checksum_Agg

Count

This function returns the number of records in a table. The Count function can be classified into 3 types.

1. count(*)
   It counts all the values including duplicate and null values in the table.
   
   Syntax
   select count(*) from student
   
   
2. Count(<col name>)
   It counts all the values including duplicate but not null values.
   
   Syntax
   select count(Name) from student
   
   
3. Count(Distinct<col name>)
   It counts the unique values.
   
   Syntax
   Select count(distinct name) from student

Distinct

This keyword is used for avoiding the duplicate values from a column in the table.

Example

Create a table.

create table student(Id int ,Name varchar(50),city varchar(50),Marks int)

Insert some values in the table.

insert into student values(1,'A','J',56)
insert into student values(2,'B','N',56)
insert into student values(3,'C','G',96)
insert into student values(4,'D','D',81)
insert into student values(5,'E','A',51)
insert into student values(6,'F','H',66)
insert into student values(7,'G','B',76)
insert into student values(7,'G','B',76)

Check the data in table.

select * from student

Now, check the count() function.

select COUNT(*) from student

 Result

 

 

Select distinct Record.

select COUNT(distinct Name) as DistinctRecord from student

Result

 

 

Avg()

It returns the average of a column value.

select avg( Marks)As Avg from student

Result

 

 

Min()

This function returns the lowest value from the given values.

select min( Marks)As MinimumMark from student

 Result

 

 

 

Max()

This function returns the highest value from the given values.

select max( Marks)As MaxMark from student

Sum()

This function returns the sum of the given values.

select sum( Marks)As TotalMark from student

Count_Big()

Count_Big is similar to the count function. The only difference is that count_big returns a bigint data type whereas the count function returns data type is int.

select COUNT_big(*) from student

Summary 

In this article, we learned about aggregate functions in SQL Server.