SQL Aggregate Functions

What Are SQL Aggregate Functions?

SQL aggregate functions perform calculations on a set of values and return a single value. These functions are commonly used in conjunction with the GROUP BY clause to group rows with similar values for aggregation.

Types of Aggregate Functions in SQL

Function	Description
COUNT()	Counts the number of rows.
SUM()	Calculates the total sum of a numeric column.
AVG()	Calculates the average value of a numeric column.
MIN()	Finds the minimum value in a column.
MAX()	Finds the maximum value in a column.

1. SQL COUNT() Function

The COUNT() function returns the total number of rows in a dataset or the number of non-NULL values in a specific column.

Example

Retrieve the total number of employees.

SELECT COUNT(*)
FROM employees;

Retrieve the number of employees in the "IT" department.

SELECT COUNT(employee_id)
FROM employees
WHERE department = 'IT';

2. SQL SUM() Function

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

Example

Retrieve the total salary of all employees.

SELECT SUM(salary)
FROM employees;

Retrieve the total sales for each product.

SELECT product_id, SUM(sales)
FROM orders
GROUP BY product_id;

3. SQL AVG() Function

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

Example

Retrieve the average salary of employees.

SELECT AVG(salary)
FROM employees;

Retrieve the average price of each product category.

SELECT category, AVG(price)
FROM products
GROUP BY category;

4. SQL MIN() Function

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

Example

Retrieve the minimum salary among all employees.

SELECT MIN(salary)
FROM employees;

Retrieve the earliest order date.

SELECT MIN(order_date)
FROM orders;

5. SQL MAX() Function

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

Example

Retrieve the maximum salary among all employees.

SELECT MAX(salary)
FROM employees;

Retrieve the latest order date.

SELECT MAX(order_date)
FROM orders;

Combining Aggregate Functions

You can use multiple aggregate functions in the same query to calculate various statistics.

Example

Retrieve the total, average, minimum, and maximum salary of employees.

SELECT 
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

Using Aggregate Functions with GROUP BY

Aggregate functions are often used  GROUP BY to group rows based on a column and calculate aggregated values for each group.

Example

Retrieve the total sales for each product.

SELECT product_id, SUM(sales) AS total_sales
FROM orders
GROUP BY product_id;

Retrieve the average salary of employees in each department.

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

Using Aggregate Functions with HAVING

The HAVING clause filters aggregated results. It is often used with GROUP BY.

Example

Retrieve departments with an average salary greater than 50,000.

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

Retrieve products with total sales exceeding 1,000 units.

SELECT product_id, SUM(sales) AS total_sales
FROM orders
GROUP BY product_id
HAVING SUM(sales) > 1000;

Key Points to Remember

1. Aggregate Functions Ignore NULL: By default, COUNT(), SUM(), AVG(), MIN(), and MAX() exclude NULL values.
2. Use with DISTINCT: Aggregate functions like COUNT() and SUM() can work with DISTINCT to operate on unique values.
   • Example: COUNT(DISTINCT column_name)
3. GROUP BY Is Essential: When combining aggregate functions with non-aggregated columns, use GROUP BY.

Performance Tips

1. Index Columns: Indexing columns used in aggregate functions can improve performance.
2. Filter Early: Use WHERE to filter rows before applying aggregate functions.
3. Avoid Complex Expressions: Simplify expressions in aggregate functions to reduce computation time.

Conclusion

SQL aggregate functions are indispensable for summarizing and analyzing data. From counting rows to calculating sums, averages, and ranges, they simplify complex queries and make data analysis efficient.