Aggregate functions are mathematical functions as part of SQL.
Figure 5.1 : Database schema
1. Sum
Let’s assume we’d like to get the number of kids that have in total all employees currently working in the company.
SELECT SUM(emp.Kids_number) AS nbOfKids
FROM Employee AS emp
WHERE emp.End IS NULL
We get the following table.
| {{h}} |
|---|
| {{r}} |
2. Average
Let’s assume we’d like to get the average salary of employees currently working in the company.
SELECT AVG(emp.Salary) AS averageSalary
FROM Employee AS emp
WHERE emp.End IS NULL
We get the following table.
| {{h}} |
|---|
| {{r}} |
3. Counter
Let’s assume we’d like to get the total number of departments that the company has.
SELECT COUNT(dep.ID) AS nbOfDepartments
FROM Department AS dep
We get the following table.
| {{h}} |
|---|
| {{r}} |
Recap
We now know how to make use of the aggregate functions that SQL offers. MAX() and MIN() work the same way. Applying MAX() on a given column allows to extract the biggest value in that column, whereas MIN() the lowest one. The function’s parameter is the column (attribute) on which computation has to be done.
Beginners sometimes confuse SUM() with COUNT(). SUM() computes the sum of values in a given column whereas COUNT() counts the number of records having a non–NULL value in a given column.
As regards COUNT(), in most cases we want to get the number of records that has a table, so we can omit the column :
SELECT COUNT(*) AS nbOfDepartments
FROM Department AS dep