Aggregate Function
An aggregate function is a function that performs a calculation
on a set of values, and returns a single value.
Aggregate functions are often used with the GROUP BY clause of
the SELECT statement. The GROUP BY clause splits the result-set
into groups of values and the aggregate function can be used
to return a single value for each group.
The most commonly used SQL aggregate functions are:
• MIN() - returns the smallest value within the selected
column
• MAX() - returns the largest value within the selected
column
• COUNT() - returns the number of rows in a set
• SUM() - returns the total sum of a numerical column
• AVG() - returns the average value of a numerical column
Id Name Salary
1 A 802
2 B 403
3 C 604
4 D 705
5 E 606
6 F NULL
Count the number of employees
SELECT COUNT(*) AS Total Employees FROM Employee;
-- Calculate the total salary
SELECT SUM(Salary) AS Total Salary FROM Employee;
-- Find the average salary
SELECT AVG(Salary) AS Average Salary FROM Employee;
-- Get the highest salary
SELECT MAX(Salary) AS Highest Salary FROM Employee;
-- Determine the lowest salary
SELECT MIN(Salary) AS Lowest Salary FROM Employee;
Total Employees
6
Total Salary
3120
Average Salary
624
Highest Salary
802
Lowest Salary
403
GROUP BY Statement
The GROUP BY Statement in SQL is used to arrange identical data into
groups with the help of some functions. i.e. if a particular column has the
same values in different rows then it will arrange these rows in a group.
Features
• GROUP BY clause is used with the SELECT statement.
• In the query, the GROUP BY clause is placed after
the WHERE clause.
• In the query, the GROUP BY clause is placed before
the ORDER BY clause if used.
• In the query, the Group BY clause is placed before the Having
clause.
SELECT NAME, SUM(SALARY) FROM emp
GROUP BY NAME;
HAVING Clause in GROUP BY Clause
We know that the WHERE clause is used to place conditions on columns
but what if we want to place conditions on groups? This is where the
HAVING clause comes into use.
SELECT NAME, SUM(sal) FROM Emp
GROUP BY name
HAVING SUM(sal)>3000;