MySQL —

Performing Mathematical Calculations

This tutorial will teach you what calculated fields are and how to create them. You will see examples of how to use calculated fields for both string concatenation and mathematical operations. You will also learn how to create and use aliases so that your application can refer to calculated fields more easily.

MySQL Mathematical Operators:

MySQL supports the basic mathematical operators showed in in following list. In addition, parentheses can be used to establish order of precedence:

  • +   Addition
  • -   Subtraction
  • *   Multiplication
  • /   Division

How to Test Calculations?
SELECT provides a great way to test and experiment with functions and calculations. Although SELECT is usually used to retrieve data from a table, the FROM clause may be omitted to simply access and work with expressions. For example:

  • SELECT 3 * 2; would return 6,
  • SELECT Trim(' abc '); would return abc, and
  • SELECT Now() uses the Now() function to return the current date and time.

Let's take a look at an example. The orders table contains all orders received, and the orderitems table contains the individual items within each order. The following SQL statement retrieves all the items in order number 20:

SELECT prod_id, quantity, item_price
FROM orderitems
WHERE order_num = 20;

Result:
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01   |       10 |       5.99 |
| ANV02   |        3 |       9.99 |
| TNT2    |        5 |      10.00 |
| FB      |        1 |      10.00 |
+---------+----------+------------+

The item_price column contains the per unit price for each item in an order. To expand the item price (item price multiplied by quantity ordered), you simply do the following:

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20;

Result:
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+

The expanded_price column shown in the previous output is a calculated field; the calculation is simply quantity*item_price. The client application can now use this new calculated column just as it would any other column.

Create Aliases for Calculated Fields

You can create aliases for columns or calculated fields in your SQL queries using the AS keyword. Aliases make it easier to refer to these fields. You create aliases for columns or calculated fields in the SELECT statement. The basic syntax for creating an alias is as follows:

SELECT column_or_expression AS alias_name
FROM table_name;
  • column_or_expression can be an actual column name or a calculated expression.
  • alias_name is the name you want to assign to the column or expression.

Here's an example that calculates the total price of products and gives it an alias total_price:

SELECT prod_id, item_price * quantity AS total_price
FROM orderitems
WHERE order_num = 20;

Result:
+---------+-------------+
| prod_id | total_price |
+---------+-------------+
| ANV01   |       59.90 |
| ANV02   |       29.97 |
| TNT2    |       50.00 |
| FB      |       10.00 |
+---------+-------------+