SQL Aggregate Functions

What is an aggregate function?

Most queries return one output row for each row that matches. An aggregate function is different: it scans a group of rows and produces one summarizing number. For example, instead of listing every salary in a company, you can ask for the total payroll or the average salary in a single value.

SQL provides five aggregate functions that cover the vast majority of everyday reporting needs. Each one takes a column (or an expression) as its input and returns one result.

• COUNT() returns how many rows match.
• SUM() adds up all the numeric values in a column.
• AVG() calculates the average of the numeric values.
• MIN() finds the smallest value.
• MAX() finds the largest value.

SELECT COUNT(*) AS employee_count,
       AVG(salary) AS average_salary,
       MAX(salary) AS highest_salary
FROM Employees;

Grouping the results

By default an aggregate function treats the whole table as one big group. When you want a separate summary per category, for instance the average salary in each department, you add a GROUP BY clause. The aggregate is then calculated once for every group.

SELECT department, AVG(salary) AS avg_salary
FROM Employees
GROUP BY department;