MySQL Aggregate Functions

This tutorial explains the use of MySQL aggregate functions like AVG, COUNT, SUM, MAX, MIN with the help of simple examples.

Aggregate functions are a bunch of methods that operate on a set of values. They can do calculations for us and then returns one final value.

For example, you may like to compute the sum of the data values in a given field. The following are aggregate functions that we are covering in this tutorial.

1. COUNT function
2. MIN function
3. MAX function
4. SUM function
5. AVG function

MySQL Aggregate Functions with Examples

Before we go through each of the function one by one. Let’s first have a sample data table we’ll use to demonstrate the usage.

CREATE TABLE empl (month INT, emp VARCHAR(15), dept VARCHAR(15), salary INT);

INSERT INTO empl VALUES
(1, "Oliver", "HR", 9000),
(1, "George", "IT", 8000),
(3, "Harry", "HR", 20000),
(6, "Jack", "IT", 110123),
(6, "Jacob", "SALES", 3000),
(12, "Noah", "SALES", 101000),
(12, "Charlie", "IT", 123456);

As stated above, you can apply MySQL aggregate functions on a set of data values and do some calculations. These methods would discard NULL values unless you specified.

COUNT

If you want to count total records matching a condition, then call the COUNT function to get the number. However, it returns zero when no matching rows exist in the table.

Syntax:

SELECT COUNT([DISTINCT] field_name)
FROM target_table
[WHERE test_expr];

Example

Let’s learn how to use COUNT in different situations.

-- Count total no. of employess
SELECT COUNT(*) FROM empl;

-- Count total no. of employees in HR dept
SELECT COUNT(*) FROM empl WHERE dept = "HR";

-- Count total no. of employees in each dept
SELECT COUNT(*), dept FROM empl GROUP BY dept;

-- Count total no. of employees earning 9000 or below in each dept
SELECT COUNT(*), dept FROM empl WHERE salary <= 9000 GROUP BY dept;

-- Count distinct joining months in the empl table
SELECT COUNT(DISTINCT month) FROM empl;

After running the above MySQL commands, the output is:

| 7
+-------------
| 2
+-------------
| 2	HR
| 3	IT
| 2	SALES
+-------------
| 1	HR
| 1	IT
| 1	SALES
+-------------
| 4
+-------------

MIN

If you want to find the minimum from a set of values, then call the MIN function to get the number. However, it returns zero when no matching rows exist in the table.

Syntax:

SELECT MIN(field_name)
FROM target_table
[WHERE test_expr];

Example

Let’s learn how to use MIN in different situations.

-- Find the employee with the lowest salary
SELECT MIN(salary) FROM empl;

-- Find the lowest salaries in each dept
SELECT dept, MIN(salary) FROM empl GROUP BY dept;

-- Find month-wise minimum salaries
SELECT month, MIN(salary) FROM empl GROUP BY month;

-- Find full employee detail having the lowest salary
SELECT * FROM empl 
WHERE salary = (SELECT MIN(salary) FROM empl);

After running the above MySQL commands, the output is:

| 3000
+--------------
| HR	9000
| IT	8000
| SALES	3000
+--------------
| 1	8000
| 3	20000
| 6	3000
+--------------
| 12	101000
+----------------------------
| 6	Jacob	SALES	3000
+----------------------------

MAX

If you want to find the maximum from a set of values, then call the MAX function to get the number. However, it returns zero when no matching rows exist in the table.

Syntax:

SELECT MAX(field_name)
FROM target_table
[WHERE test_expr];

Example

Let’s learn how to use MAX in different situations.

-- Find the employee with the highest salary
SELECT MAX(salary) FROM empl;

-- Find the highest salaries in each dept
SELECT dept, MAX(salary) FROM empl GROUP BY dept;

-- Find month-wise maximum salaries
SELECT month, MAX(salary) FROM empl GROUP BY month;

-- Find full employee detail having the highest salary
SELECT * FROM empl 
WHERE salary = (SELECT MAX(salary) FROM empl);

After running the above MySQL commands, the output is:

| 123456
+--------------
| HR	20000
| IT	123456
| SALES	101000
+--------------
| 1	9000
| 3	20000
| 6	110123
+--------------
| 12	123456
+------------------------------
| 12	Charlie	IT	123456
+------------------------------

SUM

If you want to the total of a set of values, then call the SUM function to get the result. However, it returns NULL when no matching rows exist in the table.

Syntax:

SELECT SUM(field_name)
FROM target_table
[WHERE test_expr];

Example

Let’s learn how to use the SUM in different situations.

-- Find the sum all employee salaries
SELECT SUM(salary) FROM empl;

-- Find the sum of salaries in each dept
SELECT dept, SUM(salary) FROM empl GROUP BY dept;

-- Find month-wise sum of salaries
SELECT month, SUM(salary) FROM empl GROUP BY month;

After running the above MySQL commands, the output is:

| 374579
+--------------
| HR	29000
| IT	241579
| SALES	104000
+--------------
| 1	17000
| 3	20000
| 6	113123
| 12	224456
+--------------

AVG

If you want to find the average of a set of values, then call the AVG function to get the result. However, it returns zero when no matching rows exist in the table.

Syntax:

SELECT AVG(field_name)
FROM target_table
[WHERE test_expr];

Example

Let’s learn how to use AVG in different situations.

-- Find the AVG of all employee salaries
SELECT AVG(salary) FROM empl;

-- Find the AVG of salaries in each dept
SELECT dept, AVG(salary) FROM empl GROUP BY dept;

-- Find month-wise AVG of salaries
SELECT month, AVG(salary) FROM empl GROUP BY month;

After running the above MySQL commands, the output is:

| 53511.2857
+-------------------
| HR	14500.0000
| IT	80526.3333
| SALES	52000.0000
+-------------------
| 1	8500.0000
| 3	20000.0000
| 6	56561.5000
| 12	112228.0000
+-------------------

Summary – MySQL Aggregate Functions

We hope that after wrapping up this tutorial, you should feel comfortable in using the MySQL Aggregate Functions. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.