Aggregate Functions in MySQL

Aggregate Functions are all about

• Performing calculations on multiple rows
• Of a single column of a table
• And returning a single value.

The ISO standard defines five (5) aggregate functions namely;

1) COUNT
2) SUM
3) AVG
4) MIN
5) MAX

Why Use Aggregate Functions

From a business perspective, different organization levels have different information requirements. Top levels managers are usually interested in knowing whole figures and not necessary the individual details.

Aggregate functions allow us to easily produce summarized data from our database.

For instance, from our myflix database , management may require following reports

• Least rented movies.
• Most rented movies.
• Average number that each movie is rented out in a month.

We easily produce above reports using aggregate functions.

Let’s look into aggregate functions in detail.

COUNT function

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

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

The table shown below shows data in movie rentals table

reference_ number	transaction_ date	return_date	membership_ number	movie_id	movie_ returned
11	20-06-2012	NULL	1	1	0
12	22-06-2012	25-06-2012	1	2	0
13	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 suppose that we want to get the number of times that the movie with id 2 has been rented out

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

Executing the above query in MySQL workbench against myflixdb gives us the following results.

COUNT('movie_id')



3

DISTINCT Keyword

The DISTINCT keyword that allows us to omit duplicates from our results. This is achieved by grouping similar values together .

To appreciate the concept of Distinct, lets execute a simple query

SELECT `movie_id` FROM `movierentals`;




movie_id


1


2


2


2


3

Now let’s execute the same query with the distinct keyword-

SELECT DISTINCT `movie_id` FROM `movierentals`;

As shown below , distinct omits duplicate records from the results.

movie_id



1


2


3

MIN function

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

As an example, let’s suppose we want to know the year in which the oldest movie in our library was released, we can use MySQL’s MIN function to get the desired information.

The following query helps us achieve that

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

Executing the above query in MySQL workbench against myflixdb gives us the following results.

MIN('year_released')



2005

MAX function

Just 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 assume we want to get the year that the latest movie in our database was released. We can easily use the MAX function to achieve that.

The following example returns the latest movie year released.

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

Executing the above query in MySQL workbench using myflixdb gives us the following results.

MAX('year_released')



2012

SUM function

Suppose we want a report that gives total amount of payments made so far. We can use the MySQL SUM function which returns the sum of all the values in the specified column. SUM works on numeric fields only. Null values are excluded from the result returned.

The following table shows the data in payments table-

payment_ id	membership_ number	payment_ date	description	amount_ paid	external_ reference _number
1	1	23-07-2012	Movie rental payment	2500	11
2	1	25-07-2012	Movie rental payment	2000	12
3	3	30-07-2012	Movie rental payment	6000	NULL

The query shown below gets the all payments made and sums them up to return a single result.

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

Executing the above query in MySQL workbench against the myflixdb gives the following results.

SUM('amount_paid')



10500

AVG function

MySQL AVG function returns the average of the values in a specified column. Just like the SUM function, it works only on numeric data types.

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

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

Executing the above query in MySQL workbench, gives us the following results.

AVG('amount_paid')



3500

Brain Teaser

You think aggregate functions are easy. Try this!

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

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`;

Executing the above example in MySQL workbench gives us the following results.

Summary

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