MS SQL
AGGREGATE FUNCTIONS
[Link] KUMAR B.E(EEE).,MBA(HRM) 1
Aggregate Functions
• Aggregate functions in SQL Server are used to perform
calculations on one or more values and return the result in
a single value.
• In SQL Server, all aggregate functions are built-in functions
that avoid NULL values except for COUNT(*).
• We mainly use these functions with the GROUP BY and
HAVING clauses of the SELECT statements in the database
query languages.
[Link] KUMAR B.E(EEE).,MBA(HRM) 2
Most commonly used aggregate functions
Aggregate Descriptions
Function
COUNT() This function counts the number of elements or rows, including
NULL values in the defined set.
SUM() This function calculates the total sum of all NON-NULL values in
the given set.
AVG() This function performs a calculation on NON-NULL values to get
the average of them in a defined set.
MIN() This function returns the minimum (lowest) value in a set.
MAX() This function returns the maximum (highest) value in a set.
[Link] KUMAR B.E(EEE).,MBA(HRM) 3
Why we use aggregate functions?
• The aggregate functions are mainly used to
produce the summarized data in economics and
finance to represent the economic health or stock
and sector performance.
• In the context of business, different organization
levels need different information, such as top
levels managers interested in knowing whole
figures and not the individual details.
[Link] KUMAR B.E(EEE).,MBA(HRM) 4
Aggregate Functions Example
CREATE DATABASE SMVEC
USE SMVEC
CREATE TABLE employee(
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
working_date date,
working_hours varchar(10),
salary INT );
[Link] KUMAR B.E(EEE).,MBA(HRM) 5
INSERT INTO employee VALUES
('Evans', ’HR Manager', '2020-10-04', 9, 25000),
('Brayden ', 'Engineer', '2020-10-04', 12, 65000),
('Huges', 'Writer', '2020-10-04', 13, 35000),
('Laura ', 'Manager', '2020-10-04', 10, 45000),
('Diego ', 'Teacher', '2020-10-04', 12, 30000),
('Antonio ', 'Writer', '2020-10-04', 13, 35000);
SELECT *FROM EMPLOYEE
[Link] KUMAR B.E(EEE).,MBA(HRM) 6
COUNT() Function
• It can also count all records based on a specified condition
and returns zero if it does not find any matching records.
• It can work with both numeric and non-numeric data types.
SELECT COUNT(*) AS total_employees FROM employee;
[Link] KUMAR B.E(EEE).,MBA(HRM) 7
SUM() Function
• This function calculates the total summation of NON-NULL
values in the given set.
• It returns NULL if the result set does not have any records.
• The SUM function can only work with the numeric data
type.
SELECT SUM(salary) AS total_salary FROM employee;
[Link] KUMAR B.E(EEE).,MBA(HRM) 8
AVG() Function
• This function calculates the average of NON-NULL
values specified in the column.
• The AVG function can only work with the numeric data type.
SELECT AVG(salary) AS total_salary FROM employee
SELECT AVG(salary) FROM employee WHERE salary>35000;
[Link] KUMAR B.E(EEE).,MBA(HRM) 9
MIN() Function
• This function gives the minimum (lowest) value of the
specified column.
• It also works with numeric data types only.
SELECT MIN(salary) AS "Lowest Salary" FROM employee;
[Link] KUMAR B.E(EEE).,MBA(HRM) 10
MAX() Function
• This function gives the maximum (highest) value of the
specified column.
• It also works with numeric data types only.
SELECT MAX(salary) AS "Highest Salary" FROM employee;
[Link] KUMAR B.E(EEE).,MBA(HRM) 11