DESCRIPTION
An aggregate function performs a calculation on a set of values, and returns a single value. Values are defined within a bracket ()
always. Except for COUNT
, aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause in the SELECT statement.
Common aggregate functions include :
- Min
- Max
- Count
- Avg
- Sum
MIN()
The MIN() function returns the smallest value from the selected column.
SELECT MIN(column_name) FROM table_name;
SELECT * FROM student; +------+--------+------+ | Id | Branch | CGPA | +------+--------+------+ | 3 | civil | 7.2 | | 2 | mech | 6.3 | | 6 | cs | 9.1 | | 4 | eee | 8.2 | | 1 | cs | 5.5 | +------+--------+------+ SELECT MIN(CGPA) FROM student; +-----------+ | MIN(CGPA) | +-----------+ | 5.5 | +-----------+
MAX()
The MAX() function returns the largest value from the selected column.
SELECT MAX(column_name) FROM table_name;
SELECT * FROM student; +------+--------+------+ | Id | Branch | CGPA | +------+--------+------+ | 3 | civil | 7.2 | | 2 | mech | 6.3 | | 6 | cs | 9.1 | | 4 | eee | 8.2 | | 1 | cs | 5.5 | +------+--------+------+ SELECT MAX(CGPA) FROM student; +-----------+ | MAX(CGPA) | +-----------+ | 9.1 | +-----------+
COUNT()
SELECT COUNT(column_name) FROM table_name;
SELECT * FROM student; +------+--------+------+ | Id | Branch | CGPA | +------+--------+------+ | 3 | civil | 7.2 | | 2 | mech | 6.3 | | 6 | cs | 9.1 | | 4 | eee | 8.2 | | 1 | cs | 5.5 | +------+--------+------+ SELECT COUNT(Id) FROM student; +-----------+ | COUNT(Id) | +-----------+ | 5 | +-----------+
AVG()
This function returns the average value of a numeric column.
SELECT AVG(column_name) FROM table_name;
SELECT * FROM student; +------+--------+------+ | Id | Branch | CGPA | +------+--------+------+ | 3 | civil | 7.2 | | 2 | mech | 6.3 | | 6 | cs | 9.1 | | 4 | eee | 8.2 | | 1 | cs | 5.5 | +------+--------+------+ SELECT AVG(CGPA) FROM student; +--------------------+ | AVG(CGPA) | +--------------------+ | 7.2600000381469725 | +--------------------+
SUM()
This function returns the total sum of a numeric column.
SELECT SUM(column_name) FROM table_name;
SELECT * FROM student; +------+--------+------+ | Id | Branch | CGPA | +------+--------+------+ | 3 | civil | 7.2 | | 2 | mech | 6.3 | | 6 | cs | 9.1 | | 4 | eee | 8.2 | | 1 | cs | 5.5 | +------+--------+------+ SELECT SUM(CGPA) FROM student; +-------------------+ | SUM(CGPA) | +-------------------+ | 36.30000019073486 | +-------------------+