What are aggregate functions in SQL?

SQL aggregate functions perform calculations on a set of rows and return a single summary value. They are commonly used with the GROUP BY clause to summarize data for each group, but can also be used to summarize an entire table, providing powerful analytical capabilities.

What Are Aggregate Functions?

Aggregate functions operate on a collection of input values and return a single value summarizing those inputs. Unlike scalar functions, which operate on a single row, aggregate functions process multiple rows to produce one result. They are essential for analytical queries and reporting, allowing users to gain insights into their data by summarizing it.

Common Aggregate Functions

The most frequently used aggregate functions in SQL include COUNT, SUM, AVG, MIN, and MAX. Each serves a distinct purpose in data summarization.

• COUNT(): Returns the number of rows or non-NULL values in a specified column. COUNT(*) counts all rows, while COUNT(column_name) counts non-NULL values.
• SUM(): Calculates the sum of all values in a numeric column. It ignores NULL values.
• AVG(): Computes the average (arithmetic mean) of all values in a numeric column. It also ignores NULL values.
• MIN(): Returns the minimum value in a column. This can apply to numeric, string, or date/time data types.
• MAX(): Returns the maximum value in a column. Similar to MIN(), it works with various data types.

Using with GROUP BY Clause

Aggregate functions are often used in conjunction with the GROUP BY clause to divide the rows into groups and perform the aggregation for each group. This allows you to get summary statistics per category, such as the average salary per department or the total sales per product.

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

Using with HAVING Clause

The HAVING clause is used to filter groups based on the results of an aggregate function. It is applied after the GROUP BY clause, whereas the WHERE clause filters individual rows before grouping. This distinction is crucial for filtering on aggregated data.

SELECT department, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 5;

The DISTINCT Keyword

The DISTINCT keyword can be used inside some aggregate functions (like COUNT, SUM, AVG) to operate only on unique values within the specified column, ignoring duplicates. This is particularly useful when you need to count unique occurrences or sum unique values.

SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;