MySQL Tutorials for Business Analyst: MySQL Aggregate Functions Tutorial

Hits: 19

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 movierentals 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 columnSUM works on numeric fields onlyNull 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

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

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!

 

 

PostgreSQL tutorial for Beginners – PostgreSQL – AVG Function

Pandas Example – Write a Pandas program to split a dataset, group by one column and get mean, min, and max values by group, also change the column name of the aggregated metric

SQL with Examples : SQL Aggregate Functions