Aggregate Functions
We will discuss five important aggregate functions: SUM, AVG, MAX, MIN, and COUNT. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows.
| · | SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric. |
| · | AVG () gives the average of the given column. |
| · | MAX () gives the largest figure in the given column. |
| · | MIN () gives the smallest figure in the given column. |
| · | COUNT(*) gives the number of rows satisfying the conditions. |
Looking at the tables at the top of the document, let's look at three examples:
SELECT SUM(SALARY), AVG(SALARY)
FROM EMPLOYEESTATISTICSTABLE;
This query shows the total of all salaries in the table, and the average salary of all of the entries in the table.
SELECT MIN(BENEFITS)
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager';
This query gives the smallest figure of the Benefits column, of the employees who are Managers, which is 12500.
SELECT COUNT(*)
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Staff';
This query tells you how many employees have Staff status (3).
