What are Aggregate Functions?

Aggregate functions perform calculations on multiple rows and return a single result. They are commonly used to summarize and analyze data in a database table. Common SQL aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().

Common Aggregate Functions

COUNT() Function

The COUNT() function returns the number of rows or non-NULL values that match the specified expression.

SELECT COUNT(*) AS total_employees
FROM employees;

COUNT() with a Column

When a column is provided to COUNT(), NULL values in that column are not included in the count.

SELECT COUNT(salary) AS employees_with_salary
FROM employees;

SUM() Function

The SUM() function calculates the total of numeric values in a column.

SELECT SUM(salary) AS total_salary
FROM employees;

AVG() Function

The AVG() function calculates the average value of a numeric column. NULL values are ignored when calculating the average.

SELECT AVG(salary) AS average_salary
FROM employees;

MIN() Function

The MIN() function returns the smallest value found in a column.

SELECT MIN(salary) AS lowest_salary
FROM employees;

MAX() Function

The MAX() function returns the largest value found in a column.

SELECT MAX(salary) AS highest_salary
FROM employees;

Using Multiple Aggregate Functions

Multiple aggregate functions can be used in the same query to produce several summary values at once.

SELECT
  COUNT(*) AS total_employees,
  SUM(salary) AS total_salary,
  AVG(salary) AS average_salary,
  MIN(salary) AS lowest_salary,
  MAX(salary) AS highest_salary
FROM employees;

Aggregate Functions with WHERE

Aggregate functions can be combined with WHERE to calculate values only for rows that meet a specific condition.

SELECT AVG(salary) AS average_salary
FROM employees
WHERE salary > 60000;

COUNT(DISTINCT)

COUNT(DISTINCT column_name) counts only unique non-NULL values in a column.

SELECT COUNT(DISTINCT department) AS total_departments
FROM employees;

Aggregate Functions with GROUP BY

Aggregate functions become especially useful with GROUP BY. This allows calculations to be performed separately for each group.

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

SUM() with GROUP BY

You can use SUM() with GROUP BY to calculate the total salary for each department.

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

COUNT() with GROUP BY

The COUNT() function can be used with GROUP BY to count the number of employees in each department.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Aggregate Functions with HAVING

The HAVING clause filters grouped results after an aggregate calculation has been performed.

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

Common Aggregate Functions

Advantages of Aggregate Functions

• Summarize large amounts of data quickly.
• Calculate totals, averages, minimums, and maximums.
• Count rows and unique values.
• Work effectively with GROUP BY.
• Can be combined with WHERE and HAVING for advanced analysis.

Best Practices

• Use meaningful aliases for calculated results.
• Use COUNT(*) when you need to count rows.
• Remember that NULL values are ignored by most aggregate functions.
• Use GROUP BY when calculations are required for separate groups.
• Use HAVING to filter grouped aggregate results.