Aggregate Functions in SQL | Complete Guide with Examples

What Are Aggregate Functions in SQL?

Aggregate functions in SQL perform calculations on multiple rows of data and return a single result. They are commonly used in reporting, summarizing data, and group-based analysis.

These functions work with the GROUP BY clause to aggregate data across categories and are often used in business reports and dashboards.




1. COUNT() – Count Records

The COUNT() function returns the number of rows that match a condition or are in a group.



Syntax:


SELECT COUNT(*) FROM employees;
SELECT COUNT(salary) FROM employees WHERE department = 'IT';
  • COUNT(*): Counts all rows (including NULLs)
  • COUNT(column): Counts only non-null values in the column


2. SUM() – Total of Numeric Column

The SUM() function returns the total sum of a numeric column.



Example:


SELECT SUM(salary) AS total_salary FROM employees;

Useful for getting totals like sales, expenses, or salaries.



3. AVG() – Average Value

The AVG() function returns the average value of a numeric column.



Example:


SELECT AVG(salary) AS average_salary FROM employees;

Automatically ignores NULL values in the column.



4. MIN() – Minimum Value

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



Example:


SELECT MIN(salary) AS lowest_salary FROM employees;

Can also be used with dates and strings.



5. MAX() – Maximum Value

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



Example:


SELECT MAX(salary) AS highest_salary FROM employees;

Can be used with numeric, date, and text types.



6. GROUP BY with Aggregate Functions

To summarize data by category, use aggregate functions with the GROUP BY clause.



Example:


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

This query calculates the average salary per department.



7. HAVING Clause with Aggregates

Use the HAVING clause to filter groups based on aggregate values (unlike WHERE, which filters rows).



Example:


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

This returns only departments with more than 10 employees.



Common Use Cases

  • Reporting: Total sales, number of users, revenue by region
  • Analysis: Average order value, most frequent customer, minimum product price
  • Dashboards: KPIs, summaries, and performance metrics


Conclusion

SQL aggregate functions like SUM(), AVG(), COUNT(), MIN(), and MAX() are vital for data analysis and report generation. They help summarize large datasets into meaningful insights and work powerfully with GROUP BY and HAVING clauses for sophisticated data analysis.