Aggregate Functions

What Are Aggregate Functions?

Aggregate functions are functions that perform a computation over a set of values and return a single summary value. They are very useful for data analysis.

The Main Aggregate Functions

COUNT() - Counting Rows

SELECT COUNT(*) FROM EMPLOYEE;

SELECT COUNT(employee_id) FROM EMPLOYEE;

SELECT COUNT(*) AS active_employee_count
FROM EMPLOYEE
WHERE status = 'Active';

SUM() - Summing Values

SELECT SUM(salary) AS total_salary FROM EMPLOYEE;

SELECT SUM(salary) AS total_salary_male
FROM EMPLOYEE
WHERE gender = 'Male';

AVG() - Average Salary

SELECT AVG(salary) AS avg_salary FROM EMPLOYEE;

SELECT ROUND(AVG(salary), 2) AS avg_salary
FROM EMPLOYEE
WHERE department = 'IT';

MIN() and MAX() - Minimum & Maximum Values

SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM EMPLOYEE;

Combining with GROUP BY

SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM EMPLOYEE
GROUP BY department;

Summary

Aggregate functions make it easy to analyze large amounts of data. Combine them with WHERE for filtering and GROUP BY for grouping.

Let's move on to Subqueries in the next chapter.