Math Operations SQL

In this medium post, we will learn about using math operations in SQL. Math can be a powerful, yet simple tool to use on your data.

Learning objectives:

• Perform basic math operations on your data.
• Discuss the order of operations for math.
• Describe analysis possibilities of using math operators and SQL together.

Here are some simple math operators.

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

Multiplication Example.

For example, we could easily create a total_order_cost column using unit_price and units_ordered.

SELECT 
productid,
units_ordered,
unit_price,
units_ordered * unit_price AS total_order_cost
FROM product

As you can see we made a new column here by using the ‘AS’ function. We saved the outcome of multiplication as a new column name.

Order of Operations.

You might recall your math classes and the order of operations. It is quite easy. In the UK, we frequently use PEMDAS to outline the order of operations. Our friends across the pond often use ‘Please excuse my dear Aunt sally’. Truthfully, it does not matter which one you use.

• Parentheses.
• Exponents.
• Multiplication.
• Division.
• Addition.
• Subtraction.

Combining Math Operations.

Let's say we want to apply a discount, the order of operations would be significant in applying this. Here is how we could achieve this:

SELECT 
productid,
quantity,
unit_price,
discount,
(unit_price - discount)*quantity AS total_order_cost
FROM product

Here we are subtracting a discount value from unit_price. The result of this is then multiplied by the quantity of that particular item and then saved AS total_order_cost.

I would also love to connect on Twitter, LinkedIn or on my Website. I am not sure what value I could provide you with but if you reach out, I would be more than happy to have a conversation or talk about what I have written here.