Aggregate Functions
Donald Green
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:
1) COUNT
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.
| COUNT('movie_id') |
|---|
| 3 |
DISTINCT keyword
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`;
| movie_id |
|---|
| one |
| 2 |
| 2 |
| 2 |
| 3 |
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.
| movie_id |
|---|
| one |
| 2 |
| 3 |
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.
| MIN('year_released') |
|---|
| 2005 |
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.
| MAX('year_released') |
|---|
| 2012 |
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.
| SUM('amount_paid') |
|---|
| 10500 |
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.
| AVG('amount_paid') |
|---|
| 3500 |
Summary
- 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.
