MySQL Part 6: Aggregations

Introduction

In this article, we discuss some simple aggregations that MySQL offers.  In Part 5 of this series, we went through some filtering and sorting functions widely used in SQL statements.  For more information about this article, click the link below.

MySQL Part 5: Refining Select Statements

To begin your MySQL session in the goormIDE, please be sure to type this code into the terminal.

mysql-ctl cli

Please ensure that the bike_shop.sql file is selected and available.  If not, double-click it on the left-hand side.  This is where we will type most of our code. If there is information saved in there, go ahead and clear out that information.

Aggregations Defined

According to the site, www.xplenty.com/glossary/what-is-sql-aggregation/, "SQL aggregation is the task of collecting a set of values to return a single value. It is done with the help of aggregate functions, such as SUM, COUNT, and AVG. For example, in a database of products, you might want to calculate the average price of the whole inventory.

How is it used?
Aggregation in SQL is typically used in conjunction with grouping. The Group By clause is used to arrange rows into groups in SQL. Aggregation, together with grouping, is key to generating quick reports and insights from a database. For example, an e-commerce company might want to see its highest-spending customers over a given time period."

SUM Aggregation

The SUM function will add up all records for a given set of data.  With no GROUP BY, the most basic SUM function will simply add up all the records for a particular column or columns.  In the example below, if we want to sum up the number of orders in our orderlines table, the query would include the following.

USE bike_shop_app;

SELECT
    SUM(quantity)
    FROM orderlines;

The query above did not include any GROUP BY statement.  The GROUP BY will allow you to aggregate at different levels.  For example, if we want to know the total number of orders by customer_id, we can add a GROUP BY statement to our query.  See the example below for more detail.

USE bike_shop_app;

SELECT
    SUM(quantity)
    FROM orderlines

    GROUP BY customer_id;

In the results above, customer_id #9 purchased a total of 16 bikes.

AVG Aggregation

The average function or AVG is almost identical to the SUM function, except that it takes an average of the records it is grouping by.  For example, the query below calculates the average price per bike.

USE bike_shop_app;

SELECT
    AVG(price)
    FROM bikes;

COUNT Aggregation

The COUNT function is also helpful when counting the number of records based on specific criteria.  The most straightforward use of the function is counting the number of records within a table.  Below, we count the number of records for all three of the bike_shop_app tables.

USE bike_shop_app;

SELECT COUNT(*) 
    FROM bikes;
    
SELECT COUNT(*) 
    FROM orderlines;
    
SELECT COUNT(*) 
    FROM customers;

You can use the COUNT and GROUP BY functions to count the number of records each customer appears in the table below.  

USE bike_shop_app;
    
SELECT 
    customer_id,
    COUNT(*) AS COUNT
    FROM orderlines
    
    GROUP BY customer_id;

Other Popular Aggregations

Other popular aggregations, including MIN and MAX, are similar to the functions used in Excel.  They can be plugged into the statements above and utilized the same way SUM, AVG, and COUNT have been used.  We will use some of these functions in future posts in more advanced queries.  In the next article, we will discuss date functions.

MySQL Part 7: Dates

For a complete list of the entire set of MySQL-related articles, please see the links below.

MySQL Part 1: goormIDE
MySQL Part 2: Create Database
MySQL Part 3: Create Tables
MySQL Part 4: String Functions
MySQL Part 5: Refining Select Statements
MySQL Part 6: Aggregations
MySQL Part 7: Dates
MySQL Part 8: Logical Operations
MySQL Part 9: Joins