Aggregate Functions (SQL)

Aggregate functions allow you to compute values from all or a subset of all of the data.

SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM   EMPLOYEE

There are different types of aggregations we can use:

• SUM(x): find the summation of all of the values of $x$ in the rows
• MIN(x): find the smallest value of $x$ in the rows
• MAX(x): find the largest value of $x$ in the rows
• AVG(x): find the average value of $x$ in the rows
• COUNT(x): count the number of rows of $x$ in the data This will not include NULL values.
• COUNT(DISTINCT x): count the number of rows of distinct occurrences of $x$ in the data
• COUNT(*): count the total number of rows

We may also use aggregations in sub-queries:

SELECT name
FROM   EMPLOYEE
WHERE (
	SELECT COUNT(*)
	FROM   DEPENDENT
	WHERE  ssn=ess
) >= 2

This will find all the names of the employees who have two or more dependents.