Those functions which are used to perform an operation on set of values and finally returns a single value are known as Aggregate Functions. In MySQL, these functions are often used with a GROUP BY clause to group values into subsets. For example, COUNT(), MAX(), MIN(), AVG(), SUM(), etc, are aggregate functions in MySQL.
This tutorial will help you learn most commonly used MySQL Aggregate Functions with the help of examples. Here are the list of aggregate functions that we are going to learn about:
Now, to understand all these aggregate function properly, we need some sample data on which we could apply these functions. Let's create a table called Products and feed some sample data into this.
I will be using MySQL shell to execute all these queries. So, let's open MySQL shell and follow me to do all these stuff.
Create products table as follows:
CREATE TABLE `products` (
`productid` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`mrp` decimal(8,2) NOT NULL DEFAULT '0.00',
`price` decimal(8,2) NOT NULL DEFAULT '0.00',
`created_at` timestamp NULL DEFAULT NOW(),
`updated_at` timestamp NULL DEFAULT NOW(),
PRIMARY KEY (`productid`),
UNIQUE KEY `products_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Next, insert some dummy data into products table:
INSERT INTO `products`
(`productid`, `name`, `mrp`, `price`)
VALUES
(1, 'Red Shirt', '999', '899'),
(2, 'Yellow Shirt', '1099', '999'),
(3, 'Blue Jeans', '1199', '1099'),
(4, 'Black Jeans', '1299', '1099'),
(5, 'Black T-shirt', '1499', '1299'),
(6, 'Blue Shirt', '999', '799'),
(7, 'Green Jacket', '3459', '2999'),
(8, 'Black Shirt', '1249', '989'),
(9, 'Black Trouser', '1599', '979'),
(10, 'Black Jacket', '2099', '1599');
Now, let's verify our sample data into products table using a simple query;
mysql> SELECT * FROM `products`;
+-----------+---------------+---------+---------+---------------------+---------------------+
| productid | name | mrp | price | created_at | updated_at |
+-----------+---------------+---------+---------+---------------------+---------------------+
| 1 | Red Shirt | 999.00 | 899.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 2 | Yellow Shirt | 1099.00 | 999.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 3 | Blue Jeans | 1199.00 | 1099.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 4 | Black Jeans | 1299.00 | 1099.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 5 | Black T-shirt | 1499.00 | 1299.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 6 | Blue Shirt | 999.00 | 799.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 7 | Green Jacket | 3459.00 | 2999.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 8 | Black Shirt | 1249.00 | 989.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 9 | Black Trouser | 1599.00 | 979.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
| 10 | Black Jacket | 2099.00 | 1599.00 | 2022-08-26 11:52:15 | 2022-08-26 11:52:15 |
+-----------+---------------+---------+---------+---------------------+---------------------+
10 rows in set (0.00 sec)
Great, we have some sample data with us to apply aggregate functions on it. Let's go one-by-one and try to understand above mentioned aggregate functions.
This function is used to return a count of the number of rows returned by the SELECT query. If the SELECT query returns no rows, COUNT() will return 0 as the result. The result of the COUNT() function is a BIGINT value
Example: Check number of products whose MRP is greater than 1000.
mysql> SELECT COUNT(*) FROM `products` WHERE `mrp` > 1000;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
This function is used to return the count of a number of different non-NULL values. If no rows are found, COUNT(DISTINCT) will return 0 as a result.
Example: Check number of products whose MRP is different and PRICE is less than 1000 .
mysql> SELECT COUNT(DISTINCT `mrp`) FROM `products` WHERE `price` < 1000;
+-----------------------+
| COUNT(DISTINCT `mrp`) |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)
This function is used to return the average value of the field supplied to it. It adds all values of a specified column together and divides it by the number of rows returned by the SELECT statement to get the average value. If SELECT statement does not find any matching rows, AVG() will return NULL as a result.
Example: Find the average of PRICE column in products table.
mysql> SELECT AVG(`price`) FROM `products` WHERE `price` BETWEEN 1000 AND 1500;
+--------------+
| AVG(`price`) |
+--------------+
| 1165.666667 |
+--------------+
1 row in set (0.01 sec)
This function is used to return the sum of the values of the field supplied to it. If no rows found by SELECT statement, SUM() will return NULL as a result. You can also use DISTINCT keyword to find sum of only the distinct values.
Example: Find the sum of MRP column in products table.
mysql> SELECT SUM(`mrp`) FROM `products` WHERE `mrp` > 1500;
+------------+
| SUM(`mrp`) |
+------------+
| 7157.00 |
+------------+
1 row in set (0.00 sec)
This function is used to return the maximum value of the field supplied to it. MAX() may also accept a string as an argument; in such scenario, MAX() will return the maximum string value. You may also use DISTINCT keyword to find maximum of the distinct values. If no rows found, MAX() will return NULL as a result.
Example: Find the maximum MRP from the products table.
mysql> SELECT MAX(`mrp`) FROM `products`;
+------------+
| MAX(`mrp`) |
+------------+
| 3459.00 |
+------------+
1 row in set (0.00 sec)
This function is used to return the minimum value of the field supplied to it. MIN() may also accept a string as an argument; in such scenario, MIN() will return the minimum string value. You may also use DISTINCT keyword to find minimum of the distinct values. If no rows found, MIN() will return NULL as a result.
Example: Find the minimum MRP from the products table.
mysql> SELECT MIN(`mrp`) FROM `products`;
+------------+
| MIN(`mrp`) |
+------------+
| 999.00 |
+------------+
1 row in set (0.01 sec)
This function is used to return the concatenated string of the values of the field supplied to it. It concatenates non-NULL values from a group. It will return NULL if no non-NULL values found.
Example: Find concatenated name of all the products names from the products table if MRP is > 1500.
mysql> SELECT GROUP_CONCAT(`name`) FROM `products` WHERE `mrp` > 1500;
+-----------------------------------------+
| GROUP_CONCAT(`name`) |
+-----------------------------------------+
| Green Jacket,Black Trouser,Black Jacket |
+-----------------------------------------+
1 row in set (0.01 sec)
BONUS Tips: We can also use multiple aggregate functions in single query.
mysql> SELECT SUM(`price`), MAX(`price`), MIN(`price`), AVG(`price`) FROM `products`;
+--------------+--------------+--------------+--------------+
| SUM(`price`) | MAX(`price`) | MIN(`price`) | AVG(`price`) |
+--------------+--------------+--------------+--------------+
| 12760.00 | 2999.00 | 799.00 | 1276.000000 |
+--------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
A MySQL Event is a task that runs on a predefined schedule therefore sometimes it is referred to as Scheduled Event.
A SQL Trigger is a set of SQL statements stored in the database that is executed whenever an event that is associated with a table occurs.
A Stored Procedure is a set of SQL statements stored in the database that can be invoked by triggers, other procedures, and applications like PHP, Python, etc.