Aggregate Functions: Counting, Summing, and Averaging

Aggregate functions in MySQL are used to perform calculations on a set of values and return a single result. They are extremely useful for summarizing and analyzing data within a database.

Common Aggregate Functions

Here's a breakdown of some common aggregate functions:

COUNT()

The COUNT() function returns the number of rows that match a specified criteria. It can be used to count all rows in a table or the number of rows that meet a specific condition.

Example:

SELECT COUNT(*) FROM employees;

This query returns the total number of rows in the employees table.

SELECT COUNT(email) FROM employees WHERE department = 'Sales';

This query returns the number of employees with an email address in the 'Sales' department.

Important Note: COUNT(*) counts all rows, including those with NULL values. COUNT(column_name) counts only rows where the `column_name` is not NULL.

SUM()

The SUM() function calculates the sum of values in a specified column.

Example:

SELECT SUM(salary) FROM employees;

This query returns the total sum of salaries for all employees.

SELECT SUM(order_total) FROM orders WHERE customer_id = 123;

This query returns the total sum of all order totals for customer with ID 123.

AVG()

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

Example:

SELECT AVG(salary) FROM employees;

This query returns the average salary of all employees.

SELECT AVG(age) FROM customers WHERE city = 'New York';

This query returns the average age of customers located in New York.

Note: AVG() ignores NULL values when calculating the average.

MIN()

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

Example:

SELECT MIN(salary) FROM employees;

This query returns the lowest salary among all employees.

SELECT MIN(order_date) FROM orders;

This query returns the earliest order date in the `orders` table.

MAX()

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

Example:

SELECT MAX(salary) FROM employees;

This query returns the highest salary among all employees.

SELECT MAX(order_date) FROM orders;

This query returns the latest order date in the `orders` table.

Applying Aggregate Functions to Grouped Data

Aggregate functions are often used in conjunction with the GROUP BY clause to calculate values for specific groups of rows. This is a powerful way to get summaries for subsets of your data.

Example:

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

This query returns the number of employees in each department. It groups the rows by the department column and then applies the COUNT(*) function to each group.

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

This query calculates the average price for each product category.

You can also use HAVING clause to filter the results of the aggregated data. It filters groups based on a specified condition after the grouping has been done. This is different from WHERE, which filters rows *before* grouping.

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

This query finds the departments with an average salary greater than 60000.