MySQL - Aggregate Functions - COUNT, MIN, MAX, AVG and SUM
MySQL
In this tutorial we will learn about some of the most commonly used aggregate functions like COUNT, MIN, MAX, AVG and SUM in MySQL.
What are Aggregate Functions in MySQL?
These are the functions in MySQL that performs some calculation on a set of values and then returns a single value.
Example: The SUM(column_name) function computes the sum of the values present in a given column.
List of aggregate functions that we will cover in this tutorial.
The orders table
For this tutorial we will be using the orders table shown below.
mysql> SELECT * FROM orders;
+---------+------------+--------+-------------+---------------------+---------------------+
| orderid | employeeid | amount | orderstatus | lastmodified | created |
+---------+------------+--------+-------------+---------------------+---------------------+
| 1 | e03 | 15.00 | OPEN | 2018-01-02 03:04:05 | 2018-01-02 03:04:05 |
| 2 | e01 | 25.50 | OPEN | 2018-01-04 03:04:03 | 2018-01-04 03:04:03 |
| 3 | e05 | 100.70 | CLOSED | 2018-02-02 03:03:04 | 2018-02-02 03:03:04 |
| 4 | e02 | 22.18 | OPEN | 2018-01-02 03:04:05 | 2018-01-02 03:04:05 |
| 5 | e04 | 9.50 | CANCELLED | 2018-01-04 03:04:03 | 2018-01-04 03:04:03 |
| 6 | e04 | 99.99 | OPEN | 2018-02-02 03:03:04 | 2018-02-02 03:03:04 |
+---------+------------+--------+-------------+---------------------+---------------------+
6 rows in set (0.00 sec)
COUNT
We use the COUNT function to find the number of rows matching the given condition.
Syntax:
SELECT COUNT(column_name)
FROM table_name;
In the following example we are listing total number of orders in the orders table.
mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set (0.01 sec)
So, there are total 6 orders in the orders table.
We can even use an alias in the above query.
mysql> SELECT COUNT(*) AS total_order FROM orders;
+-------------+
| total_order |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
COUNT with WHERE clause
Syntax:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
In the following example we are counting total number of orders placed by employeeid 'e04'.
mysql> SELECT COUNT(*) AS total_order FROM orders WHERE employeeid = 'e04';
+-------------+
| total_order |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
So, employeeid 'e04' placed total 2 orders.
COUNT DISTINCT
If we want to count distinct value in a column then we use DISTINCT in the COUNT function.
Syntax:
SELECT COUNT(DISTINCT column_name)
FROM table_name;
In the following example we are counting total number of distinct employees who placed an order.
mysql> SELECT COUNT(DISTINCT employeeid) AS total_unique_employeeids FROM orders;
+--------------------------+
| total_unique_employeeids |
+--------------------------+
| 5 |
+--------------------------+
1 row in set (0.00 sec)
So, total 5 unique employeeid is present in the orders table.
MIN
We use the MIN function to find the minimum value.
Syntax:
SELECT MIN(column_name)
FROM table_name;
In the following example we will display the minimum order amount in the orders table.
mysql> SELECT MIN(amount) FROM orders;
+-------------+
| MIN(amount) |
+-------------+
| 9.50 |
+-------------+
1 row in set (0.00 sec)
So, minimum order amount is 9.50 in the orders table.
We can use an alias for the above query.
mysql> SELECT MIN(amount) AS min_amount FROM orders;
+------------+
| min_amount |
+------------+
| 9.50 |
+------------+
1 row in set (0.00 sec)
MIN with WHERE clause
Syntax:
SELECT MIN(column_name)
FROM table_name
WHERE condition;
In the following example we will display the minimum order amount for the employeeid 'e03' in the orders table.
mysql> SELECT MIN(amount) AS min_amount FROM orders WHERE employeeid = 'e03';
+------------+
| min_amount |
+------------+
| 15.00 |
+------------+
1 row in set (0.00 sec)
So, employeeid 'e03' has a minimum order amount of 15.00.
MAX
We use the MAX function to find the maximum value.
Syntax:
SELECT MAX(column_name)
FROM table_name;
In the following example we will display the maximum order amount in the orders table.
mysql> SELECT MAX(amount) FROM orders;
+-------------+
| MAX(amount) |
+-------------+
| 100.70 |
+-------------+
1 row in set (0.00 sec)
So, maximum or highest order amount in the orders table is 100.70.
We can use an alias for the given query like the following.
mysql> SELECT MAX(amount) AS max_amount FROM orders;
+------------+
| max_amount |
+------------+
| 100.70 |
+------------+
1 row in set (0.00 sec)
MAX with WHERE clause
Syntax:
SELECT MAX(column_name)
FROM table_name
WHERE condition;
In the following example we will display the max order amount for the employeeid 'e01' in the orders table.
mysql> SELECT MAX(amount) AS max_amount FROM orders WHERE employeeid = 'e01';
+------------+
| max_amount |
+------------+
| 25.50 |
+------------+
1 row in set (0.00 sec)
So, employeeid 'e01' has placed max value order of 25.50.
AVG
We use the AVG function to find the average.
Syntax:
SELECT AVG(column_name)
FROM table_name;
In the following example we will display the average order amount in the orders table.
mysql> SELECT AVG(amount) FROM orders;
+-------------+
| AVG(amount) |
+-------------+
| 45.478333 |
+-------------+
1 row in set (0.00 sec)
So, the average order amount in the orders table is 45.478333.
We can also use an alias in the above query.
mysql> SELECT AVG(amount) AS avg_amount FROM orders;
+------------+
| avg_amount |
+------------+
| 45.478333 |
+------------+
1 row in set (0.00 sec)
AVG with WHERE clause
Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
In the following example we will display the average order amount for the employeeid 'e04' in the orders table.
mysql> SELECT AVG(amount) AS avg_amount FROM orders WHERE employeeid = 'e04';
+------------+
| avg_amount |
+------------+
| 54.745000 |
+------------+
1 row in set (0.00 sec)
So, average order amount placed by employeeid 'e04' is 54.745000.
SUM
We use the SUM function to find the sum.
Syntax:
SELECT SUM(column_name)
FROM table_name;
In the following example we will display the sum i.e., total of the order amount in the orders table.
mysql> SELECT SUM(amount) FROM orders;
+-------------+
| SUM(amount) |
+-------------+
| 272.87 |
+-------------+
1 row in set (0.00 sec)
So, total order amount in the orders table is 272.87.
When using an alias the output will look like the following.
mysql> SELECT SUM(amount) AS total_amount FROM orders;
+--------------+
| total_amount |
+--------------+
| 272.87 |
+--------------+
1 row in set (0.00 sec)
SUM with WHERE clause
Syntax:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
In the following example we will display the sum i.e., total of the order amount for OPEN orders in the orders table.
mysql> SELECT SUM(amount) AS open_order_total_amount FROM orders WHERE orderstatus = 'OPEN';
+-------------------------+
| open_order_total_amount |
+-------------------------+
| 162.67 |
+-------------------------+
1 row in set (0.00 sec)
So, total order amount for all the orders that are in OPEN state is 162.67.
ADVERTISEMENT