MySQL 8 Aggregate Functions: SUM, AVG, MIN, MAX, COUNT

Introduction

MySQL is a widely-used, open-source relational database management system. One of MySQL 8’s key features is its robust set of aggregate functions. These functions allow you to perform calculations on data sets, such as the total sum or average of numbers. This tutorial will deep dive into the aggregate functions: SUM, AVG, MIN, MAX, and COUNT, and provide you with a clear understanding through practical examples.

Prerequisites

• Basic knowledge of SQL and MySQL.
• MySQL 8 installed on your machine or access to a MySQL 8 server.
• A database and table with some sample data to work with.

Understanding Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. They ignore NULL values, except for the COUNT function. Before we move to individual functions, let’s create a sample table and insert data into it for our examples.

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    quantity INT,
    price DECIMAL(10, 2)
);

INSERT INTO sales (product, quantity, price) VALUES
    ('Widget A', 10, 2.50),
    ('Widget B', 5, 3.00),
    ('Widget C', 8, 1.75),
    ('Widget B', 3, 3.00),
    ('Widget A', 7, 2.50),
    ('Widget C', 2, 1.75);

SUM Function

The SUM function calculates the total sum of a numeric column. Here is a basic example:

SELECT SUM(quantity) as total_quantity FROM sales;

Output:

+----------------+
| total_quantity |
+----------------+
|             35 |
+----------------+

Notice how SUM() aggregates the total quantity sold across all products.

AVG Function

The AVG function returns the average value of a numeric column. Below is an example of how to use it:

SELECT AVG(quantity) as average_quantity FROM sales;

Output:

+------------------+
| average_quantity |
+------------------+
|           5.8333 |
+------------------+

The average quantity sold for all the products is approximately 5.83.

MIN and MAX Functions

The MIN function returns the smallest value in a set, and the MAX function returns the largest value. They can be used on numeric as well as non-numeric columns. Here are two examples:

SELECT MIN(price) as minimum_price FROM sales;
SELECT MAX(price) as maximum_price FROM sales;

Output:

+--------------+
| minimum_price |
+--------------+
|          1.75 |
+--------------+

+--------------+
| maximum_price |
+--------------+
|           3.00 |
+--------------+

The minimum price among all the products is $1.75, and the maximum is $3.00.

COUNT Function

The COUNT function returns the number of rows matching a certain criteria. Here’s an example that counts all the rows in the table:

SELECT COUNT(*) as total_rows FROM sales;

Output:

+-----------+
| total_rows |
+-----------+
|         6 |
+-----------+

The table contains a total of 6 rows.

Combining Aggregate Functions

It’s common to use aggregate functions together especially in reports. Here’s how you could use MIN, MAX and AVG functions to give a summary of the price:

SELECT
  MIN(price) as minimum_price,
  MAX(price) as maximum_price,
  AVG(price) as average_price
FROM
  sales;

Output:

+--------------+--------------+--------------+
| minimum_price | maximum_price | average_price |
+--------------+--------------+--------------+
|          1.75   |          3.00 |         2.5833 |
+--------------+--------------+--------------+

Advanced Use of Aggregate Functions

Aggregate functions become even more powerful when used with GROUP BY and WITH ROLLUP clauses. With GROUP BY, you can produce a summary for each unique value in a column. WITH ROLLUP adds a row that contains a subtotal for each group. Given below is an example of both:

SELECT product, SUM(quantity) as product_quantity FROM sales GROUP BY product WITH ROLLUP;

Output:

+---------+------------------+
| product | product_quantity |
+---------+------------------+
| Widget A |               17 |
| Widget B |                8 |
| Widget C |               10 |
| NULL    |               35 |
+---------+------------------+

The NULL row represents the total sum (sum with roll up).

Conclusion

In this tutorial, we’ve explored the fundamental aggregate functions of MySQL 8. You’ve learned how to calculate sums, averages, minimums, maximums, and counts over your data sets. These functions are crucial tools in your SQL arsenal for data analysis and reporting.