SQL Aggregate Functions
SQL aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include AVG(), COUNT(), MAX(), MIN(), and SUM().
AVG() Function
The AVG() function returns the average value of a numeric column.
SELECT AVG(salary) AS average_salary FROM employees;This query calculates the average salary of all employees.
COUNT() Function
The COUNT() function returns the number of rows that match a specified condition.
SELECT COUNT(*) AS number_of_employees FROM employees;This query counts the total number of employees.
MAX() Function
The MAX() function returns the largest value of the selected column.
SELECT MAX(salary) AS highest_salary FROM employees;This query finds the highest salary among all employees.
MIN() Function
The MIN() function returns the smallest value of the selected column.
SELECT MIN(salary) AS lowest_salary FROM employees;This query finds the lowest salary among all employees.
SUM() Function
The SUM() function returns the total sum of a numeric column.
SELECT SUM(salary) AS total_salary FROM employees;This query calculates the total salary of all employees.
GROUP BY Clause
The GROUP BY clause is often used with aggregate functions to group the result set by one or more columns.
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;This query calculates the average salary for each department.
HAVING Clause
The HAVING clause is used to filter groups based on a condition, similar to the WHERE clause but for groups.
SELECT department, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;This query finds departments with more than 10 employees.
Example 1: Combining Aggregate Functions
SELECT department, COUNT(*) AS number_of_employees, AVG(salary) AS average_salary
FROM employees
GROUP BY department;This query returns the number of employees and the average salary for each department.
Example 2: Using Aggregate Functions with JOIN
SELECT departments.name AS department, COUNT(employees.id) AS number_of_employees
FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY departments.name;This query returns the number of employees in each department, joining the “employees” and “departments” tables.
Aggregate functions are essential for summarizing and analyzing data in SQL databases. They provide a powerful way to calculate statistics and insights from your data.