Aggregate Functions

Aggregate Functions

Aggregate functions are all about

  • Perform calculations in multiple rows
  • From one table column
  • And returning a single value.

The ISO standard defines five (5) aggregate functions, namely:


2) SUM

3) AVG

4) MIN

5) MAX 

Why use aggregate functions.

From a business perspective, different levels of an organization have different information requirements. Top-level managers are generally interested in knowing whole numbers and don't need individual details. 

> Aggregate functions allow us to easily retrieve summary data from our database. 

For example, from our myflix database, management may require the following reports

  • Least Rented Films.
  • The most rented films.
  • The average number each film is rented per month.

We easily create the above reports using aggregate functions.

Let's look at aggregate functions in detail.

COUNT function

The COUNT function returns the total number of values ​​in the specified field. It works with both numeric and non-numeric data types. All aggregate functions exclude null values ​​by default before working with data.

COUNT(*) is a special implementation of the COUNT function that returns the count of all rows in the specified table. COUNT(*) also counts Null and duplicates.

The table below shows the data in the movierentals table

reference number Deal date Return date membership number movie_id movie is back
eleven 20-06-2012 NULL one one 0
12 22-06-2012 25-06-2012 one 2 0
thirteen 22-06-2012 25-06-2012 3 2 0
14 21-06-2012 24-06-2012 2 2 0
15 23-06-2012 NULL 3 3 0

Let's say we want to get the number of times a movie with id 2 has been rented out

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Running the above query in MySQL Workbench against myflixdb gives us the following results.


DISTINCT keyword

MySQL Aggregate Functions Tutorial: SUM, AVG, MAX, MIN, COUNT, DISTINCT

The DISTINCT keyword, which allows us to exclude duplicates from our results. This is achieved by grouping similar values ​​together.

To appreciate the concept of Distinct, let's run a simple query

SELECT `movie_id` FROM `movierentals`;

Now let's execute the same query with separate keyword 

SELECT DISTINCT `movie_id` FROM `movierentals`;

As shown below, a report on excluding duplicate entries from the results.



MIN function

The MIN function returns the smallest value in the specified table field .

As an example, let's say we want to know the year the oldest movie in our library was released, we can use MySQL's MIN function to get the required information.

The following query helps us achieve this

SELECT MIN(`year_released`) FROM `movies`;


Running the above query in MySQL Workbench against myflixdb gives us the following results.


Max function

As the name suggests, the MAX function is the opposite of the MIN function. It returns the largest value from the specified table field .

Let's say we want to get the year the last movie was released in our database. We can easily use the MAX function to achieve this.

The following example returns the latest release year of a movie.

SELECT MAX(`year_released`) FROM `movies`;


Running the above query in MySQL Workbench using myflixdb gives us the following results.



SUM function

Suppose we want to get a report showing the total amount of payments made. We can use the MySQL SUM function which returns the sum of all values ​​in a given column . SUM only works with numeric fields . Null values ​​are excluded from the returned result.

The following table summarizes the data in the payments table.

payment ID membership number payment date description paid amount external_reference_number
one one 07/23/2012 Film rental payment 2500 eleven
2 one 25-07-2012 Film rental payment 2000 12
3 3 30-07-2012 Film rental payment 6000 NULL

The query below returns all payments made and sums them up to get a single result.

SELECT SUM(`amount_paid`) FROM `payments`;

Running the above query in MySQL Workbench for myflixdb gives the following results.


AVG function

The MySQL AVG function returns the mean value in the specified column . Like the SUM function, it only works with numeric data types .

 Suppose we want to find the average amount paid. We can use the following query −

SELECT AVG(`amount_paid`) FROM `payments`;

Running the above query in MySQL Workbench gives us the following results.



  • MySQL supports all five (5) standard ISO COUNT aggregate functions, SUM, AVG, MIN and MAX.
  • The SUM and AVG functions only work with numeric data.
  • If you want to exclude duplicate values ​​from the results of an aggregate function, use the DISTINCT keyword. The ALL keyword includes even duplicates. If nothing is specified, the default is ALL.
  • Aggregate functions can be used in conjunction with other SQL clauses such as GROUP BY

brain teaser

You think aggregate functions are simple. Try it!

The following example groups members by name, counts the total number of payments, the average amount of payments, and the total amount of payments.

SELECT m.`full_names`,COUNT(p.`payment_id`) AS `paymentscount`,AVG(p.`amount_paid`) AS `averagepaymentamount`,SUM(p.`amount_paid`) AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;

Running the above example in MySQL Workbench gives us the following results.