SQL Server Aggregate Functions

SQL Server Aggregate Functions

An aggregate function allows you to perform a calculation on a set of values to return a single scalar value. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.

In SQL Server we have got lot of aggregate functions. Examples

1. Count()

2. Sum()

3. Avg()

4. Min()

5. Max()

Demo Table :(For better understand)

EmpID	Name	Gender	Salary
1	Top	Male	20000
2	Pam	Female	3000
3	John	Male	3500
4	Sam	Male	4500
5	Todd	Male	2800
6	Ben	Male	7000
7	Sara	Female	4800
8	Valarie	Female	5500
9	James	Male	6500
10	Russell	Male	8800

1. Count()

The COUNT() function returns the number of rows that matches a specified criteria.

The following SQL statement finds the number of Employee:

Example

select Count(Emp_Id) as TotalEmployee from Employee;

Output

TotalEmployee

10

Note: NULL values are not counted.

2. Sum()

The SUM() function returns the total sum of a numeric column.

The following SQL statement finds the sum of the "Salary" fields in the "Employee" table:

Example

select SUM(salary) as TotalEmployeeSalary from Employee;

Output

TotalEmployeeSalary

66400

3. Avg()

The AVG() function returns the average value of a numeric column.

The following SQL statement finds the average Salary of all Employee.

Example

select avg(salary) as AverageSalary from Employee;

Output

AverageSalary

6640

4. Min()

The MIN() function returns the smallest value of the selected column.

The following SQL statement finds the Minimum Salary from the Employee table.

Example

select min(salary)as MinSalary from Employee;

Output

MinSalary

2800

5. Max()

The MAX() function returns the largest value of the selected column.

The following SQL statement finds the Maximum Salary from the Employee table.

Example

Select MAX(salary) as MaxSalary from Employee;

Output

MaxSalary

20000