Aggregate Functions

Aggregate functions perform calculations on multiple rows of data and return a single value. They are commonly used with the GROUP BY clause to group rows into subsets.

Common aggregate functions include:

• COUNT() - Counts the number of rows
• SUM() - Calculates the sum of a numeric column
• AVG() - Calculates the average of a numeric column
• MIN() - Finds the minimum value in a column
• MAX() - Finds the maximum value in a column

The COUNT() function returns the number of rows that match a specified condition.

-- Count all employees
SELECT COUNT(*) AS total_employees FROM employees;

-- Count employees in each department
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

SUM() calculates the total of a numeric column, while AVG() calculates the average.

-- Calculate total salary expenditure
SELECT SUM(salary) AS total_payroll FROM employees;

-- Calculate average salary by department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

MIN() and MAX() functions find the smallest and largest values in a column respectively.

-- Find the highest salary
SELECT MAX(salary) AS highest_salary FROM employees;

-- Find the earliest hire date by department
SELECT department, MIN(hire_date) AS earliest_hire
FROM employees
GROUP BY department;

The GROUP BY clause groups rows with the same values in specified columns. The HAVING clause filters groups based on aggregate function results.

-- Departments with more than 2 employees
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

-- Departments with average salary above 70000
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;