Introduction
An aggregate function performs a calculation one or more values and returns a single value. The aggregate function is often used with the GROUP BY clause and HAVING clause of the SELECT statement. Common aggregate functions are
- MAX() : Returns the highest value (maximum) in a set of non-NULL values.
- MIN() : Returns the lowest value (minimum) in a set of non-NULL values.
- COUNT() : Returns the number of rows in a group, including rows with NULL values.
- AVG() : Calculates the average of non-NULL values in a set.
- SUM() : Returns the summation of all non-NULL values a set.
Finding Maximum and Minimum
MAX() function is an aggregate function that returns the maximum value in a set. MIN() function is an aggregate function that returns the minimum value in a set. Syntax of these functions are
MAX(expression) MIN(expression)
These function accepts an expression that can be a column or a valid expression. They ignores NULL values and considers all values in the calculation.
Following example finds the minimum and maximum salary in each age group using GROUP BY clause. It also finds the overall maximum and minimum salary.
/* Create Table */ CREATE TABLE emp( id INT, name VARCHAR(10), age INT, salary INT ) /* Insert record */ INSERT INTO emp(id,name,age,salary) VALUES(1, 'Rohan', 34, 5000), (2, 'Scott', 43, 10000), (3, 'Anu', 34, 8000), (4, 'Vishal', 30, 5000), (5, 'Shane', 25, 8000), (6, 'Rohit', 40, 10000) /* Query to find maximum and minimum salary */ SELECT MIN(salary) AS Min_Salary, MAX(salary) AS Max_Salary FROM emp /* Result of Query */ 5000 10000 /* Query to find maximum and minimum salary in each age group */ SELECT age, MIN(salary) AS Min_Salary, MAX(salary) AS Max_Salary FROM emp GROUP BY age ORDER BY age /* Result of Query */ age Min_Salary Max_Salary 25 8000 8000 30 5000 5000 34 5000 8000 40 10000 10000 43 10000 10000
Count
COUNT() is an aggregate function that returns the number of items found in a set. Following shows the syntax of the COUNT() function:
COUNT([ALL | DISTINCT] expression) COUNT(*)
- COUNT(*) does not support DISTINCT and takes no parameters. It counts the number of items in a set.
- COUNT(ALL expression) evaluates the expression for each row in a set and returns the number of non-null values.
- COUNT(DISTINCT expression) evaluates the expression for each row in a set, and returns the number of unique, non-null values.
Below example finds distinct salaries and salaries in each age group in emp table.
/* Query to find distinct salary */ SELECT COUNT(DISTINCT salary) AS Distint_Count FROM emp /* Result of Query */ 3 /* Query to count number of salaries */ SELECT COUNT(ALL salary) AS All_Salary FROM emp SELECT COUNT(*) salary FROM emp /* Result of Query */ 6 6 /* Query to count number of salaries in each age group */ SELECT age, COUNT(salary) AS S_Count FROM emp GROUP BY age ORDER BY age /* Result of Query */ age S_Count 25 1 30 1 34 2 40 1 43 1
Average and Sum
AVG() function is an aggregate function that returns the average value of a group. SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression. Syntax of the functions are
AVG([ALL | DISTINCT] expression) SUM([ALL | DISTINCT ] expression)
- ALL instructs the function to take all values for calculation. ALL is used by default.
- DISTINCT instructs the function to operate only on unique values.
- expression is a valid expression that returns a numeric value.
Following statement finds the sum of distinct salaries, all salaries and sum of salaries in each age group in emp table.
/* Query to find sum of all salaries */ SELECT SUM(ALL salary) AS S_Sum FROM emp /* Result of Query */ 46000 /* Query to find sum of distinct salaries */ SELECT SUM(DISTINCT salary) AS S_Sum FROM emp /* Result of Query */ 23000 /* Query to find sum of all salaries in each age group */ SELECT age, SUM(salary) AS S_Sum FROM emp GROUP BY age ORDER BY age /* Result of Query */ age S_Sum 25 8000 30 5000 34 13000 40 10000 43 10000