In SQL Server, aggregate functions are used to perform calculations on a set of values and return a single value. These functions operate on multiple rows of a table and return a single value based on the specified criteria. In this blog, we will discuss the aggregate functions available in SQL Server.
COUNT
Function:
The COUNT
function returns the number of rows that match the specified criteria. It can
be used with the asterisk (*) wildcard to count all rows in a table.
Syntax:
COUNT(* | [ALL|DISTINCT]
expression)
Example:
SELECT COUNT(*) AS TotalEmployees
FROM Employees
In this
example, the COUNT function is used to count the total number of rows in the
Employees table.
SUM
Function:
The SUM
function returns the sum of all values in a column.
Syntax:
SUM([ALL|DISTINCT]
expression)
Example:
SELECT SUM(Salary) AS TotalSalary FROM Employees
In this
example, the SUM function is used to calculate the total salary of all
employees in the Employees table.
AVG
Function:
The AVG
function returns the average value of a column.
Syntax:
AVG([ALL|DISTINCT]
expression)
Example:
SELECT AVG(Salary) AS AverageSalary FROM Employees
In this
example, the AVG function is used to calculate the average salary of all
employees in the Employees table.
MIN
Function:
The MIN
function returns the minimum value in a column.
Syntax:
MIN([ALL|DISTINCT]
expression)
Example:
SELECT MIN(Salary) AS MinimumSalary FROM Employees
In this
example, the MIN function is used to find the minimum salary of all employees
in the Employees table.
MAX
Function:
The MAX
function returns the maximum value in a column.
Syntax:
MAX([ALL|DISTINCT]
expression)
Example:
SELECT MAX(Salary) AS MaximumSalary FROM Employees
In this
example, the MAX function is used to find the maximum salary of all employees
in the Employees table.
GROUP BY
Function:
The GROUP
BY function is used to group rows with the same values in a column and perform
aggregate functions on each group.
Syntax:
SELECT
column_name, aggregate_function(column_name)
FROM
table_name
GROUP BY column_name;
Example:
SELECT
Department, SUM(Salary) AS TotalSalary FROM Employees
GROUP BY Department
In this
example, the GROUP BY function is used to group employees by department and
calculate the total salary of each department.
HAVING
Function:
The HAVING
function is used with the GROUP BY function to filter groups based on specific
criteria.
Syntax:
SELECT
column_name, aggregate_function(column_name)
FROM
table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
Example:
SELECT
Department, SUM(Salary) AS TotalSalary FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 100000
In this
example, the HAVING function is used to filter departments whose total salary
is greater than 100000.
Conclusion:
In SQL
Server, aggregate functions are used to perform calculations on a set of values
and return a single value. The most commonly used aggregate functions are
COUNT, SUM, AVG, MIN, and MAX. The GROUP BY and HAVING functions are used to
group and filter rows based on specific criteria. By using these functions, we
can easily summarize data and perform complex calculations on large sets of
data in SQL Server.
Reference
Comments
Post a Comment