How To Use Mathematical Expressions and Aggregate Functions in SQL

Introduction

Structured Query Language (SQL) is used to store, manage, and organize information in a relational database management system (RDBMS). SQL can also perform calculations and manipulate data through expressions. Expressions combine various SQL operators, functions, and values, to calculate a value. Mathematical expressions are commonly used to add, subtract, divide, and multiply numerical values. Additionally, aggregate functions are used to evaluate and group values to generate a summary, such as the average or sum of values in a given column. Mathematical and aggregate expressions can provide valuable insights through data analysis that can inform future decision-making.

In this tutorial, you’ll practice using mathematical expressions. First, you’ll use numeric operations on a calculator, then use those operators on sample data to perform queries with aggregate functions, and finish with a business scenario to query sample data for more complex information and analysis.

Prerequisites for Mathematical Expressions and Aggregate Functions in SQL

To complete this tutorial, you will need:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL installed and secured on the server. Follow our How To Install MySQL on Ubuntu 20.04 guide to set this up. This guide assumes you’ve also set up a non-root MySQL user, as outlined in Step 3 of this guide.

Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.

To practice many of the mathematical expression examples in this tutorial, you’ll need a database and table loaded with sample data. If you do not have one ready to insert, you can read the following Connecting to MySQL and Setting up a Sample Database section to learn how to create a database and table. This tutorial will refer to this sample database and table throughout.

Connecting to MySQL and Setting up a Sample Database

If your SQL database runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Next, open the MySQL prompt, replacing sammy with your MySQL user account information:

mysql -u sammy -p

Create a database named mathDB:

CREATE DATABASE mathDB;

If the database was created successfully, you’ll receive the following output:

Query OK, 1 row affected (0.01 sec)

To select the mathDB database, run the following USE statement:

USE mathDB;

Output:

Database changed

After selecting the database, create a table within it using the CREATE TABLE command. For this tutorial’s example, we’ll create a table named product_information to store inventory and sales information for a small tea shop. This table will hold the following eight columns:

• product_id: represents the values of the int data type and will serve as the table’s primary key. This means each value in this column will function as a unique identifier for its respective row.
• product_name: details the names of the products using the varchar data type with a maximum of 30 characters.
• product_type: stores the types of products, as demonstrated by the varchar data type with a maximum of 30 characters.
• total_inventory: represents how many units of each product are left in storage, using the int data type with a maximum of 200.
• product_cost: displays the price of each product purchased at cost using the decimal data type with a maximum of 3 values to the left, and 2 values after the decimal point.
• product_retail: stores prices for each product sold at retail, as shown by the decimal data type with a maximum of 3 values to the left, and 2 values after the decimal point.
• store_units: using values of the int data type, displays how many units of the specific product are available for in-store sales inventory.
• online_units: represents how many units of the specific product are available for online sales inventory using values of the int data type.

Creating and Populating the Sample Table for Mathematical Expressions and Aggregate Functions in SQL

Create this sample table by running the following command:

      CREATE TABLE product_information (
        product_id int, 
        product_name varchar(30), 
        product_type varchar(30), 
        total_inventory int(200),
        product_cost decimal(3, 2), 
        product_retail decimal(3, 2), 
        store_units int(100),
        online_units int(100),
        PRIMARY KEY (product_id)
      );
    

Output:

      Query OK, 0 rows affected, 0 warnings (0.01 sec)
    

Now insert some sample data into the empty table:

      INSERT INTO product_information
      (product_id, product_name, product_type, total_inventory, product_cost, product_retail, store_units, online_units)
      VALUES
      (1, 'chamomile', 'tea', 200, 5.12, 7.50, 38, 52),
      (2, 'chai', 'tea', 100, 7.40, 9.00, 17, 27),
      (3, 'lavender', 'tea', 200, 5.12, 7.50, 50, 112),
      (4, 'english_breakfast', 'tea', 150, 5.12, 7.50, 22, 74),
      (5, 'jasmine', 'tea', 150, 6.17, 7.50, 33, 92),
      (6, 'matcha', 'tea', 100, 6.17, 7.50, 12, 41),
      (7, 'oolong', 'tea', 75, 7.40, 9.00, 10, 29),
      (8, 'tea sampler', 'tea', 50, 6.00, 8.50, 18, 25),
      (9, 'ceramic teapot', 'tea item', 30, 7.00, 9.75, 8, 15),
      (10, 'golden teaspoon', 'tea item', 100, 2.00, 5.00, 18, 67);
    

Output:

      Query OK, 10 rows affected (0.01 sec)
      Records: 10  Duplicates: 0  Warnings: 0
    

Calculating with Mathematical Expressions and Aggregate Functions in SQL

In SQL, you typically use SELECT to query your database and retrieve the desired result set. However, you can also use the SELECT keyword to perform a variety of mathematical operations.

Keep in mind that in a real-life scenario, SQL is primarily used to query and make calculations from values in your actual database. But for this section, you’ll use SELECT solely for numerical values to get familiar with the syntax of mathematical expressions and operators.

Overview of Mathematical Operators

Before you begin, here’s an overview of the operators you can use to perform six arithmetic operations in SQL:

• Addition uses the + symbol
• Subtraction uses the - symbol
• Multiplication uses the * symbol
• Division uses the / symbol
• Modulo operations use the % symbol
• Exponentiation uses POW(x, y)

You can practice running different types of calculations with your own value combinations. Here are some examples:

      SELECT 893 + 579;
      SELECT 437.82 - 66.34;
      SELECT 60 * 1234 * 2 * 117;
      SELECT 2604.56 / 41;
      SELECT 38 % 5;
      SELECT POW(99, 9);
    

Order of Operations in SQL

SQL follows the order of operations, often abbreviated as PEMDAS: Parentheses, Exponents, Multiplication and Division (from left to right), and Addition and Subtraction (from left to right).

Here’s an example:

      SELECT (2 + 4) * 8;
      SELECT 2 + (4 * 8);
    

Analyzing Data with Aggregate Functions

Aggregate functions in SQL include SUM, MAX, MIN, AVG, and COUNT. These functions help summarize data for analysis. Here are some examples:

      SELECT SUM(total_inventory) FROM product_information;
      SELECT MAX(product_cost) AS cost_max FROM product_information;
      SELECT MIN(product_retail) AS retail_min FROM product_information;
      SELECT AVG(product_retail) AS retail_average, AVG(product_cost) AS cost_average FROM product_information;
      SELECT COUNT(product_retail) FROM product_information WHERE product_retail > 8.00;
    

Applying Mathematical Expressions in a Business Scenario

Use the following queries to analyze the tea shop’s business data:

      SELECT product_name, total_inventory - (store_units + online_units) AS remaining_inventory 
      FROM product_information ORDER BY remaining_inventory DESC;

      SELECT product_name, (online_units * product_retail) AS online_revenue, (store_units * product_retail) AS store_revenue 
      FROM product_information;

      SELECT SUM(online_units * product_retail) + SUM(store_units * product_retail) AS total_sales 
      FROM product_information;

      SELECT product_name, (product_retail - product_cost) / product_retail AS profit_margin 
      FROM product_information;

      SELECT AVG((product_retail - product_cost) / product_retail) AS avg_profit_margin 
      FROM product_information;

      SELECT product_name, product_cost / (1 - 0.31) AS new_retail 
      FROM product_information WHERE (product_retail - product_cost) / product_retail < 0.27;
    

Conclusion for Mathematical Expressions and Aggregate Functions in SQL

Using mathematical expressions in SQL can range from solving arithmetic problems like you would on a calculator, to performing complex analyses on real-world data that may influence business decisions. Recognizing the primary mathematical operators and rules for orders of operations allows you to explore endless possibilities for calculations. Additionally, using aggregate functions can provide answers to critical questions that help with strategic planning. Learn more about what you can do with databases in SQL with our series on How To Use SQL.