How to work with aggregate functions in MySQL

Aggregate functions are those that perform calculations in a field and return a single value. For example, an aggregate function can calculate the sum of a certain numeric field that has a pattern in common or that meet certain criteria.

These functions are useful when we want to summarize data by an entity. The aggregate functions can help us to know the billing totals per client, which customer is the one that has bought the most, which is the average sales per seller, count the times a customer has bought, among others.

Let’s see how to use the aggregate functions in MySQL.

We will focus on the five most used: AVG, MIN, MAX, COUNT, and SUM.

Before explaining how each function works, we will create two tables to apply the functions on them.

The two tables will have the following structure and content:

1. /***************/
2. /*CLIENTS TABLE*/
3. /***************/
4. CREATE TABLE `clients` (
5. `id` bigint(20) UNSIGNED NOT NULL,
6. `name` varchar(255) DEFAULT NULL,
7. `address` text
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10. INSERT INTO `clients` (`id`, `name`, `address`) VALUES
11. (1, 'Mark Johnson', '2113 Jackson Street,Apt. B,Erie,CA,510'),
12. (2, 'Sasha Smith', '123 Main Street,,Erie,CA,512'),
13. (3, 'Charles Walls', '979 Drummond Street, Newark');
15. ALTER TABLE `clients` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `id` (`id`);
17. ALTER TABLE `clients` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
19. /***************/
20. /*ORDERS TABLE*/
21. /***************/
22. CREATE TABLE `orders` (
23. `id` int(11) NOT NULL,
24. `client_id` int(11) NOT NULL,
25. `received` datetime NOT NULL,
26. `processed` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
27. `total` float NOT NULL,
28. `paid` float NOT NULL DEFAULT '0'
29. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31. INSERT INTO `orders` (`id`, `client_id`, `received`, `processed`, `total`, `paid`) VALUES
32. (1, 1, '2019-05-19 13:18:13', '2019-05-20 09:11:57', 100, 75),
33. (2, 1, '2019-05-19 13:21:18', '2019-05-20 15:32:14', 50, 50),
34. (3, 2, '2019-05-20 14:19:18', '2019-05-20 21:27:30', 75, 60),
35. (4, 2, '2019-05-20 15:47:15', '2019-05-21 08:23:28', 60, 60),
36. (5, 3, '2019-05-21 08:23:15', '2019-05-21 10:33:24', 120, 100),
37. (6, 1, '2019-01-22 05:20:08', '2019-02-25 12:10:19', 500, 200),
38. (7, 1, '2019-05-23 13:18:13', '2019-05-24 09:11:57', 150, 150);
40. ALTER TABLE `orders` ADD PRIMARY KEY (`id`);
41. ALTER TABLE `orders` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

MySQL AVG function

This function allows us to calculate the average of a numerical field. For example, let’s imagine that we want to know the average of general sales.

1. SELECT AVG(a.total) FROM `orders` a

Result:

AVG(a.total)

150.71428571428572

Let’s make it more interesting, let’s imagine that we want to know the sales average for each client.

1. SELECT a.name as `Client`, AVG(b.total) as `AVG`
2. FROM `clients` a JOIN `orders` b ON a.id = b.client_id
3. GROUP BY a.name

The GROUP BY clause allows us to group the results by customer name.

Result:

Client	AVG
Charles Walls	120
Mark Johnson	200
Sasha Smith	67.5

MySQL MIN function

The MIN function allows us to calculate which is the lowest value of a field in a given query. For example, let’s imagine we want to know which is the lowest sale we have registered.

1. SELECT MIN(a.total) as `MIN` FROM `orders` a

Result: 50

MySQL MAX function

The MAX function allows us to calculate which is the hihgest value of a field in a given query. For example, let’s imagine we want to know which is the hihgest sale we have registered.

1. SELECT MAX(a.total) as `MAX` FROM `orders` a

Result: 500

MySQL COUNT function

The COUNT function allows us to count the elements that satisfy the clauses of the query we are building. For example, let’s imagine we want to know many sales we have registered.

1. SELECT COUNT(a.total) as `COUNT` FROM `orders` a

Result: 7

MySQL SUM function

The SUM function allows us to calculate the sum of a numeric field in a given query. For example, let’s imagine that we want to know how much we have sold in general.

1. SELECT SUM(a.total) as `SUM` FROM `orders` a

Result: 1,055

MySQL HAVING clause

We already saw how the five most common aggregate functions in MySQL work. Now let’s see how to add clauses to those functions. For example, if we want to find the client that has bought most times:

1. SELECT b.name, COUNT(a.total) as `COUNT`
2. FROM `orders` a
3. JOIN `clients` b
4. ON a.client_id = b.id
5. GROUP BY a.client_id, b.name
6. HAVING COUNT >= ALL (SELECT COUNT(b.total) as `COUNT` FROM `orders` b GROUP BY b.client_id )

Result:

name	COUNT
Mark Johnson	4