What are aggregate functions in SQL?

Aggregate functions in SQL are operations that calculate a single value from a set of rows in a database table. They are commonly used to summarize or analyze data, such as counting records, calculating averages, or finding maximum and minimum values. The most frequently used aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX(). For example, COUNT(*) returns the total number of rows in a table, while SUM(sales) adds all values in the sales column. These functions are often paired with the GROUP BY clause to organize results into groups, enabling analysis of subsets of data, like sales per region or average salary per department.

A key aspect of aggregate functions is their interaction with grouping and filtering. When combined with GROUP BY, they process each group separately. For instance, SELECT department, AVG(salary) FROM employees GROUP BY department calculates the average salary for each department. The HAVING clause is then used to filter groups after aggregation, unlike WHERE, which filters rows before aggregation. For example, SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000 filters out departments with average salaries below 50,000. This distinction between WHERE and HAVING is critical for writing accurate queries.

Aggregate functions also handle NULL values differently. Most functions, like SUM() or AVG(), ignore NULLs entirely. For example, AVG(salary) excludes rows where salary is NULL. However, COUNT(*) counts all rows regardless of NULLs, while COUNT(column) counts only non-NULL values in that column. Developers can also use DISTINCT within aggregates to process unique values, such as COUNT(DISTINCT department) to count unique departments. Advanced functions like STDDEV() (standard deviation) or VAR() (variance) provide statistical insights. Understanding these nuances ensures accurate data summaries, which are essential for reporting, dashboards, or data-driven decision-making in applications.