Use MySQL Aggregate Functions in Multiple Tables with Subqueries

Introduction

MySQL aggregate functions are in-built self-contained modules that perform calculations on multiple user-defined values and return a single value. The most common examples include: SUM(), AVG(), MIN() and the MAX() functions.

Aggregate functions come in handy when creating a summary-value for reporting purposes based on different tables in a database. Instead of saving summary data permanently into a table, you simply retrieve a dynamic value based on a result of a function. This makes your MySQL applications more scalable and easier to maintain.

In this tutorial, you'll use the MySQL aggregate SUM() function and a couple of subqueries to retrieve the current stock of products in a sample store database.

Prerequisites

Before you begin, make sure you have the following:

• A MySQL database server.

Set Up a Sample Database

Login to your MySQL database server as a privileged user that can create databases(e.g. the root user).

$ sudo mysql -u root -p

Enter the root password of your MySQL database instance and hit Enter to continue. Then, set up a sample store database:

mysql> CREATE DATABASE store;

Make store the currently active database.

mysql> USE store;

Create the Base Table

Next, create a products table. This is the base table that will hold the product_id, product_name, price, and opening_stock of the items that you're currently offering in your hypothetical store.

mysql> CREATE TABLE products (
       product_id INT AUTO_INCREMENT PRIMARY KEY, 
       product_name VARCHAR(50), 
       price DOUBLE, 
       opening_stock DOUBLE                 
       );

Populate the products table with some records by running the commands below one by one. When running the INSERTScommands, you don't have to provide a value for the product_id column as this will be automatically handled by the database since you've designed the field with the AUTO_INCREMENT keyword. The opening_stock column will store the initial quantity of the product when it is first entered to the products table.

mysql> INSERT INTO products (product_name, price, opening_stock) VALUES ('RAIN JACKET', '47.23', '7.0');
mysql> INSERT INTO products (product_name, price, opening_stock) VALUES ('SPORT SHOES', '48.69', '14.0');
mysql> INSERT INTO products (product_name, price, opening_stock) VALUES ('REXIN BELT', '14.77', '1.0');

Run the SELECT command below to verify the records on the products table.

mysql> SELECT
       product_id,
       product_name,
       price,
       opening_stock         
       from products;

Make sure the records are inserted successfully as shown below.

+------------+--------------+-------+---------------+
| product_id | product_name | price | opening_stock |
+------------+--------------+-------+---------------+
|          1 | RAIN JACKET  | 47.23 |             7 |
|          2 | SPORT SHOES  | 48.69 |            14 |
|          3 | REXIN BELT   | 14.77 |             1 |
+------------+--------------+-------+---------------+
3 rows in set (0.00 sec)

Create the Purchased Products Table

Create a purchases table. This table will store the number of products purchased. To uniquely identify the products, you'll use a product_id column which will refer back to the same column on the products table. The quantity_purchased field will store the purchased quantity of the product(inward flow of stock).

mysql> CREATE TABLE purchased_products (
       purchase_id INT AUTO_INCREMENT PRIMARY KEY,
       purchase_date DATE, 
       product_id INT, 
       quantity_purchased DOUBLE                            
       );

Populate the purchased_products table. You must use product id's 1, 2 and3 since these are the only items available on the main products table for purchases/sales.

mysql> INSERT INTO purchased_products (purchase_date, product_id, quantity_purchased) VALUES ('2020-01-16', '1', '3');
mysql> INSERT INTO purchased_products (purchase_date, product_id, quantity_purchased) VALUES ('2020-01-16', '2', '6');
mysql> INSERT INTO purchased_products (purchase_date, product_id, quantity_purchased) VALUES ('2020-01-16', '3', '8');

Ensure that the purchased_products table was indeed populated.

mysql> SELECT
       purchase_id,
       purchase_date, 
       product_id, 
       quantity_purchased
       FROM purchased_products;

You should get an output similar to the one shown below:

+-------------+---------------+------------+--------------------+
| purchase_id | purchase_date | product_id | quantity_purchased |
+-------------+---------------+------------+--------------------+
|           1 | 2020-01-16    |          1 |                  3 |
|           2 | 2020-01-16    |          2 |                  6 |
|           3 | 2020-01-16    |          3 |                  8 |
+-------------+---------------+------------+--------------------+
3 rows in set (0.00 sec)

So apart from the opening stock that you've on the base products table, the output above confirms that you've purchased more products for your items.

Create the Sold Products Table

Next, create a sold_products table. This table will store all sales going out of your store. Like the purchased_products table, you'll use the product_id column in this table to refer to the items that you've on the base products table. The quantity_sold column represents the outward flow of stock. In other words, an entry in this table reduces the stock in your store.

Create the table.

mysql> CREATE TABLE sold_products (
       sales_id INT AUTO_INCREMENT PRIMARY KEY,
       sales_date DATE, 
       product_id INT, 
       quantity_sold DOUBLE                            
       );

Enter some sales to the sold_products table.

mysql> INSERT INTO sold_products (sales_date, product_id, quantity_sold) VALUES ('2020-01-16', '1', '4');
mysql> INSERT INTO sold_products (sales_date, product_id, quantity_sold) VALUES ('2020-01-16', '2', '3');
mysql> INSERT INTO sold_products (sales_date, product_id, quantity_sold) VALUES ('2020-01-16', '3', '1');

Verify the data from the sold_products table.

mysql> SELECT
       sales_id,
       sales_date, 
       product_id, 
       quantity_sold
       FROM sold_products;

Make sure the sales were recorded in the table by confirming the output below.

+----------+------------+------------+---------------+
| sales_id | sales_date | product_id | quantity_sold |
+----------+------------+------------+---------------+
|        1 | 2020-01-16 |          1 |             4 |
|        2 | 2020-01-16 |          2 |             3 |
|        3 | 2020-01-16 |          3 |             1 |
+----------+------------+------------+---------------+
3 rows in set (0.00 sec)

Use MySQL Aggregate Function to Retrieve Current Stock

You now have 3 tables in your database. As indicated earlier, the products is the base table that stores the opening stock while the purchased_products and sold_products tables handle inflow and outflow of stock respectively.

To get the current stock of each product for reporting purposes or to enforce a logical check when customers are ordering products from your store, you can use the formula below.

current_stock = opening_stock(from base products table) + purchases(from purchased_products) - sales(from sold_products)

Translate the formula above to SQL by running the query shown below.

mysql> SELECT
       products.product_id,
       products.product_name,
       products.price,
       (
           products.opening_stock
           +
           (
               SELECT SUM(quantity_purchased)
               FROM purchased_products
               WHERE product_id = products.product_id    
           )
           -
           (
               SELECT SUM(quantity_sold)
               FROM sold_products
               WHERE product_id = products.product_id    
           )
       ) as current_stock    
       FROM products;

The Aggregate SQL query explained:

Opening Stock:

...
products.opening_stock 
...

The code above retrieves the initial quantity of each product from the base products table.

Quantity Purchased:

...
+
(
    SELECT SUM(quantity_purchased)
    FROM purchased_products
    WHERE product_id = products.product_id    
)
...

The above SQL statement selects the sum of purchased items from the purchased_products for the product_id that matches each row. Please note the + at the top as this increments the stock.

Quantity Sold:

... 
-
(
    SELECT SUM(quantity_sold)
    FROM sold_products
    WHERE product_id = products.product_id    
)
...

Finally, the code snippet above retrieves the sum of items going out of the store as sales. The - operator reduces the stock in the formula.

Run the complete query and ensure you get the output shown below:

+------------+--------------+-------+---------------+
| product_id | product_name | price | current_stock |
+------------+--------------+-------+---------------+
|          1 | RAIN JACKET  | 47.23 |             6 |
|          2 | SPORT SHOES  | 48.69 |            17 |
|          3 | REXIN BELT   | 14.77 |             8 |
+------------+--------------+-------+---------------+
3 rows in set (0.00 sec)

You can verify the formula is working since in the products table, the opening stock for the REXIN BELT was 1, Then, on the purchased_products table 8 products were acquired while only 1 item was sold in the sold_products table. So, to get a current stock count for the REXIN BELT the query has implemented the the formula below.

current-stock for REXIN BELT(product_id 3) = 1 + 8 - 1 = 8

Similarly, for the SPORT SHOES, the formula implemented is.

current-stock for SPORT SHOES(product_id 2) = 14 + 6 - 3 = 17

The same function is applied to the RAIN JACKET product.

current-stock for RAIN JACKET(product_id 1) = 7 + 3 - 4 = 6

Check the Aggregate Function After a New Insert

To verify that the current_stock calculated column is indeed a result of the aggregate function from the 3 tables, insert a new sales record for the REXIN BELT into the sold_products table and see if the current stock will be re-calculated.

mysql> INSERT INTO sold_products (sales_date, product_id, quantity_sold) VALUES ('2020-01-16', '3', '6');

Run the aggregate function statement one more but this time around, filter the records using the SQL statement WHERE product_id = '3' to get the stock for REXIN BELT` only.

mysql> SELECT
       products.product_id,
       products.product_name,
       products.price,
       (
           products.opening_stock
           +
           (
           SELECT SUM(quantity_purchased)
           FROM purchased_products
           WHERE product_id = products.product_id    
           )
           -
           (
           SELECT SUM(quantity_sold)
           FROM sold_products
           WHERE product_id = products.product_id    
           )
       ) as current_stock    
       FROM products
       WHERE product_id = '3';

The output below confirms that the aggregate function is working. Before the last INSERT statement, the current stock for REXIN BELT was 8 and since you've recorded a sales of 6 products, the stock has reduced to 2 items

+------------+--------------+-------+---------------+
| product_id | product_name | price | current_stock |
+------------+--------------+-------+---------------+
|          3 | REXIN BELT   | 14.77 |             2 |
+------------+--------------+-------+---------------+
1 row in set (0.00 sec)

Conclusion

In this guide, you've used MySQL aggregate functions and subqueries to derive columns for reporting purposes. This is a quick and accurate way that you can implement in your applications to create summaries for different related tables.