Aggregate Functions in SQL

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 |
+-------------------+