Aggregate Functions

There are seven aggregate functions in SQL which return a single value which is calculated from the values in a column. These seven aggregate functions are as follows:

1. SUM() returns the sum of the values in a column.
2. AVG() returns the average of the values in a column.
3. COUNT() returns the number of rows in the column.
4. MAX() returns the largest value in the given column.
5. MIN() returns the smallest value in the given column.
6. FIRST() returns the first value in the column.
7. LAST() returns the last value in the column.

---

SUM()

The SUM() function returns the sum of the values in a column.
Syntax:

SELECT SUM(column_name) FROM table_name;

Below is a table, marks_maria, showing marks of Maria in a particular year.

Subject	I term	II term	III term	IV term
Maths	90	78	75	80
English	72	68	76	62
French	78	75	63	58
Science	93	88	84	75

Total marks obtained by Maria in Ist semester can be calculated by the following query:

SELECT SUM(I term) FROM marks_maria;

The output will be:

SUM(I term)
333

---

AVG()

The AVG() function returns the average of the values in a column.
Syntax:

SELECT AVG(column_name) FROM table_name;

Hence, the average marks of Maria in Ist term can be calculated as follows:

SELECT AVG(I term) AS average FROM marks_maria;

The output will be:

average
83.25

---

COUNT()

The COUNT() function returns the number of rows in a particular column, table or based on a particular condition.
Syntax:

SELECT COUNT(column_name) FROM table_name;

A simple example to calculate the number of times Maria has scored more than 90 in Ist term is:

SELECT COUNT(I term) AS good_score
FROM marks_maria
WHERE I term >90;

The output will be:

good_score
1

To calculate the total number of records in a table, we can use COUNT(*) statement.
Syntax:

SELECT COUNT(*) FROM table_name;

To calculate the total number of records in the above table, the SQL is:

SELECT COUNT(*) AS total_records FROM marks_maria;

And the output will be:

total_records
4

You can also use COUNT (DISTINCT) statement to display the distinct values in that column.
Syntax:

SELECT COUNT(DISTINCT column_name) FROM table_name;

---

MAX()

The MAX() function returns the maximum value in the column.
Syntax:

SELECT MAX(column_name) FROM table_name;

To find out what is the highest marks that Maria obtained in IInd term, the SQL will be:

SELECT MAX(II term), Subject FROM marks_maria;

The output will be:

MAX(II term)	Subject
88	Science

---

MIN()

The MIN() funtion returns the minimum value in the column.
Syntax:

SELECT MIN(column_name) FROM table_name;

To select the minimum marks that Maria scored in IInd term, the SQL query will be:

SELECT MIN(II term), Subject FROM marks_maria;

The output will be:

MIN(II term)	Subject
68	English

---

FIRST()

The FIRST() function returns the first value of the column.
Syntax:

SELECT FIRST(column_name) FROM table_name;

To find the first value in the Ist term, the SQL query will be:

SELECT FIRST(I term) FROM marks_maria;

The output will be:

FIRST(I term)
90

---

LAST()

The LAST() function will return the last value of the column.
Syntax:

SELECT LAST(column_name) FROM table_name;

The following query will return the last value of the Ist term:

SELECT LAST(I term) FROM marks_maria;

The output will be:

LAST(I term)
93