The Aggregate Functions in SQL

What is the aggregate function in MySQL?

Aggregate functions are a bunch of methods that work on a set of values. They can do calculations for us. After that, they return one final value.

The following five aggregate functions are defined by the ISO standard:

1. COUNT function
2. MIN function
3. MAX function
4. SUM function
5. AVG function

Example Table

create table employee_details (Id int (10), Emp_name varchar (40), dept varchar (40), Emp_salary int (10));

INSERT INTO employee_details (
Id,
Emp_name,
Dept, Emp_salary)
VALUES
(1, 'Mahesh', 'HR', 50000),
(2, 'Rajesh', 'HR', 60000),
(3, 'Anil', 'Finance', 50000),
(4, 'Shikha', 'Admin', Null),
(5, 'Ritul', 'IT', 65000),
(6, 'Kamal', 'IT', 80000);


Max:

The aggregate function SQL MAX() is used to get the highest value or maximum value of a particular column or expression. This function is helpful to determine the largest of all selected values of a column.

SELECT MAX(Emp_SALARY) FROM employee_details;

Min:

The smallest value of the selected column is returned by the MIN() function.

SELECT MIN(Emp_SALARY) FROM employee_details;

Count:

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

To get the total number of rows in a table if any Null values exist in the Table:
Select Count(*) from employee_details;
To get the total number of salary in a table if any Null Values exist in the Table:

Select Count(emp_salary) from employee_details;

To get the unique values in a Table (It will not count the duplicate value, It will show only 1 in case of multiple duplicate values):

select count( DISTINCT (emp_salary)) from employee_details;

Sum:

The total sum of a numeric column is returned by the SUM() function.

To get a sum of the salary:

select sum(Emp_salary) from employee_details;

To get a sum of the salary except for the duplicate values

select sum(DISTINCT (emp_salary)) from employee_details;

Average:

The average value of a numeric column is returned by the AVG() function.

select avg(emp_salary) from employee_details;

Select avg(distinct(emp_salary)) from employee_details;

The SQL GROUP BY Statement:

The GROUP BY statement is a group of rows that have similar values into summary rows, like “find the number of consumers in each country”.
The GROUP BY statement is usually used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
To get the employees to detail in all the Dept from the Table:
select dept from employee_details group by dept;
To get the total number of employees in every dept:
select dept, count(*) from employee_details group by dept;

The SQL Having Clause:

Having Clause is commonly used with the GROUP BY clause to filter the groups based on a specified list of conditions.

To get the employee detail less than 2 in all the Dept from the Table select dept from employee_details group by dept having count(*):

select dept from employee_details group by dept having count(*)

Individuals that have a good command over English have more chances of getting hired. An English certification can help in such situations. English certification exam from StudySection includes basic as well as advanced level English language concepts.