3.3 Math Operations

Learning Objectives

• perform basic math calculations
• discuss in more detail the concept and the order of operations
• describe what can be done in terms of analysis by using math operators and SQL with math calculations

Let’s start with some simple ones. We have the addition, subtraction, multiplication, and division.

SELECT 
ProductID,
UnitsOnOrder,
UnitPrice,
UnitsOnOrder * UnitPrice AS Total_Order_Cost
FROM Products

In this example, we get the total order cost by multiplying units on order with unit price. And then, we name the new column Total_Order_Cost by using AS.

You dont’t have to SELECT the unit price and the units on the order to calculate the total cost. You could have just selected the product ID and then calculated the new field. But it is a nice thing to make sure it all adds up and looks right.

Now let’s add these operators together. Just as any math you’re doing, it’s going to follow normal order of operations. You probably remember the order of operations from past math classes. Operations in parentheses are handled first, then power, multiplication, division, addition, and subtraction.

SELECT
ProductID,
Quantity,
UnitPrice,
Discount,
(UnitPrice - Discount)/Quantity AS Total_Cost
FROM Products