SQL Aggregate Functions: Explore 5 Types of Functions
Aggregate Function: In SQL server data manipulation and interaction with database is most common thing. To write a different types of SQL queries we require SQL functions to calculate data, by using SQL functions, we can turn data as per our requirement.
Aggregate Functions is one of the Most used SQL functions that are used calculated number of count values, average values, total values, minimum and maximum values by using different aggregate function.
SQL Aggregate Function are used to for Summary data retrieval using different Aggregate functions with Group by clause. In this Tutorial we will learn All Aggregate functions with their syntax and examples.
Table of Contents
What is Aggregate Function?
In SSMS Aggregate Functions in SQL server that are the most used to calculate the data set of values groups values to form summary data.
Aggregate Functions are used with Group by Clause, without Group by clause you cannot use Aggregate function like SUM, COUNT, AVG etc.
These Aggregate Function are work on Group of data and show us total result in single Output value with calculating all grouped values.
Commonly used clause is Group by with Aggregate function, This Group by clause will merge data into groups and calculate aggregate values within the group.
Types Of Aggregate Functions
SQL server Provides different types of functions for calculations of data, some most used Aggregate functions are below.
1.SUM
2.AVG
3.COUNT
4.MAX
5.MIN
Let’s see All aggregate functions in detail with examples one by one.
1. SUM
SUM Aggregate Function is used to calculate the Total for defined columns.
Consider the example, we have a table Employee, and we have to calculate all employee’s total salary.
To calculate All employees Total Salary, we can USE SUM Aggregate function but for that we need Group by clause.
Below is the Common Syntax to use Aggregate Functions?
Aggregate Functions Syntax
Select aggregate_function(column_name),Column2
From table_name
WHERE condition
GROUP BY column_name,Column2;Explanations of Aggregate Functions
aggregate function: The function which can be used for finding Values in specified column like MIN, MAX, AVG, SUM, COUNT etc.
column_name: Column_name is the column on which you want to use Aggregate functions.
table_name: table name is name of your table from you are retrieving the data.
condition:Condition is used in where clause to filter the data as per your input values.
GROUP BY:Group by Clause is used to Groups the Rows in the tables based on Columns.
Example SUM Aggregate Function
Calculate All employees Salary.
select SUM(Salary) TotalSalary
from Employee_1Calculate Employee’s Salary Group Wise
Suppose you have to calculate Employees Total salary Department wise then use Department in Group by clause with sum in select for Salary column.
select Department,
SUM(Salary) TotalSalary_DeptWise
from Employee_1
Group by Department
2. AVG
AVG Average Aggregate function is used to calculate average value for selected column.
Consider the same example as SUM, Calculate Average Salary from Employee_1 table, with Employee_Id wise.
select Employee_ID,
AVG(Salary) AverageSalary_IDWise
from Employee_1
Group by Employee_ID
Total Average Salary of All employee’s, To calculate All employee’s Total Average Salary write an SQL statement like Below.
select AVG(Salary) As TotAverageSalary
from Employee_1
3. COUNT
Count Aggregate function can be used to count number of values in the field or to counts number of rows in the result.
Suppose we have to count number of Designation wise number of employees in the table. To retrieve number of employees, count designation wise, write below SQL Statement
select EmployeeDesignation,
count(*) Number_Of_EMP from Employee_copy
group by EmployeeDesignation
OR
select EmployeeDesignation,
count(EmployeeID) Number_Of_EMP from Employee_copy
group by EmployeeDesignation
Result of Both the SQL Query will be same, you can you Count (*) or Count (Column name) to count the number of values in the column.
To count ALL Employees in the Table, just write the below SQL statement.
---Total Employee Count----
Select Count(EmployeeID) TotalEMployeeCount
from Employee_copy
4. MAX
MAX Function is used to retrieve Highest value from the selected column. using MAX function in SQL server you can select Maximum value from that specified column.
Consider the example, you have a Table EmpMaster and you have to select Latest Date from the Date of Joining Column. then you can use MAX Aggregate function to retrieve Maximum date from the Date of Joining column.
Select MAX(EMployeeDOJ)As Maximum_Date_of_Joining
from Employee_copyConsider another example to find Highest Salary from the Table with Employee name.
Select * from EmpMaster
Select Max(Salary) Max_salary
from EmpMaster
https://itdevelopement.com/category/sql/
5. MIN
MIN Aggregate Function is used to calculate Minimum value from the column,MIN FUnction will retrive lowest value from the specified column.
Example MIN Aggregate Function
Consider the Example, suppose you have to find Minimum length name employee, Find the employee whose length of name is minimum.
Select EmployeeName from Employee_copy
where LEN(EMployeeName)=(select MIN(LEN(EMployeeName))
from Employee_copy)here we have Used LEN function to find the length of Column and then we used MIN with LEN Function to find small length EmployeeName
Find Minimum Salary from Employee_copy table.
Select MIN(EMployeeSalary) MInimum_Salary from Employee_copy
Aggregate functions are mostly used to Generate Summary report or to retrieve summary data from the table we can use Aggregate functions.
Aggregate Functions Examples
-------------------ALL Aggregate Functions Example---------------
---SUM---
-------Calculate Total Employee Salary--------------
select SUM(Salary) TotalSalary
from Employee_1
---------Calculate Department wise Total Salary------
select Department,
SUM(Salary) TotalSalary_DeptWise
from Employee_1
Group by Department
-- AVG ---
---Total Employees Average Salary----
select
AVG(Salary) TotAverageSalary
from Employee_1
---EmployeeID wise Average Salary----
select EmployeeID,
AVG(Salary) AverageSalary_IDWise
from Employee_1
Group by EmployeeID
----COUNT----
--- Designation wise emp Count---
select EmployeeDesignation,
count(*) Number_Of_EMP from Employee_copy
group by EmployeeDesignation
---Total Employee Count----
Select Count(EmployeeID) TotalEMployeeCount
from Employee_copy
---MAX---
Select MAX(EMployeeDOJ)As Maximum_Date_of_Joining
from Employee_copy
Select * from EmpMaster
Select Max(Salary) Max_salary
from EmpMaster
----MIN----
---Find Minimun Length Employee Name----
Select EmployeeName from Employee_copy
where LEN(EMployeeName)=(select MIN(LEN(EMployeeName))
from Employee_copy)
--Find Minimun Salary----
Select MIN(EMployeeSalary) MInimum_Salary from Employee_copy
References: