MySQL Aggregate Functions

Introduction

Aggregate functions in MySQL allow you to perform calculations on sets of values and return a single value as a result. They are commonly used to obtain summary information from tables or subsets of data. In this article, we will explore the usage of aggregate functions in MySQL with examples to illustrate their functionality.

COUNT()

The COUNT() function is used to count the number of rows in a table or a subset of data. Here's an example that counts the number of employees:

SELECT COUNT(*) FROM employees;

SUM()

The SUM() function is used to calculate the sum of a column or a set of values. Here's an example that calculates the total salary of all employees:

SELECT SUM(salary) FROM employees;

AVG()

The AVG() function is used to calculate the average value of a column or a set of values. Here's an example that calculates the average salary of all employees:

SELECT AVG(salary) FROM employees;

MIN()

The MIN() function is used to find the minimum value of a column or a set of values. Here's an example that retrieves the lowest salary among employees:

SELECT MIN(salary) FROM employees;

MAX()

The MAX() function is used to find the maximum value of a column or a set of values. Here's an example that retrieves the highest salary among employees:

SELECT MAX(salary) FROM employees;

GROUP BY

The GROUP BY clause is used in combination with aggregate functions to perform calculations on subsets of data. It groups the data based on one or more columns. Here's an example that calculates the total sales amount per customer:

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

HAVING

The HAVING clause is used in combination with the GROUP BY clause to filter the results of the grouped data based on conditions. Here's an example that retrieves customers with a total sales amount greater than 1000:

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000;

Conclusion

Aggregate functions are essential in MySQL for performing calculations on sets of data and obtaining summary information. In this article, we explored the usage of COUNT(), SUM(), AVG(), MIN(), and MAX() functions, as well as the GROUP BY and HAVING clauses. By leveraging aggregate functions, you can extract meaningful insights and statistics from your MySQL databases.