SQL Aggregate Function
An aggregate function is a function that performs a calculation on a set of values, and
returns a single value.
Aggregate functions ignore null values (except for COUNT()).
SQL Aggregate functions are mostly used with the GROUP BY clause of the SELECT
statement.
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 AGE CITY COUNTRY SALARY JOIN_DATE
1 Khilan 22 Pune India 57500 2022-01-14
2 Ramesh 21 Hyderabad India 25550 2023-01-02
3 Komal 23 New york USA 44200 2023-10-08
4 Kaushik 18 Muscat Oman 47275 2023-03-15
5 Chaitali 23 Mumbai India 40700 2022-04-18
6 Hardhik 19 Atlanta USA 44200 2023-06-04
MIN( ) and MAX ( ) Function:
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
Write a query to find the lowest salary in a table.
SELECT MIN(SALARY) AS LowestSalary FROM [Link];
Write a query to find the highest salary in a table.
SELECT MAX(Salary) AS HighestSalary FROM [Link];
COUNT() Function
The COUNT() function returns the number of rows that matches a specified criterion.
Syntax:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Write a query to count total records in a table.
Select COUNT(*) FROM [Link];
If you specify a column name instead of (*), NULL values will not be counted.
Select COUNT(COUNTRY) FROM [Link];
Select COUNT(DISTINCT COUNTRY) FROM [Link];
Adding a where clause in COUNT( )
SELECT COUNT(ID) FROM [Link] WHERE SALARY > 40000;
SUM() Function
The SUM() function returns the total sum of a numeric column.
Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Write a query to calculate total salary for the employees belonging to India region.
Select SUM(SALARY) AS Total_salary FROM [Link] WHERE COUNTRY='India';
AVG() Function:
The AVG function is used to calculate the average value of the numeric type. AVG function
returns the average of all non-Null values.
Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Write a query to calculate average salary for the employees whose age is greater than 20.
Select AVG(SALARY) AS Avg_salary FROM EMPLOYEE1 WHERE AGE>20;