Making calculations in columns

Creating your own columns in SQL becomes even more useful for business analysis when you include calculations in the columns.

For example, in the order_line table, you’ll find:

• The quantity of what has been bought
• The price per unit.

It could be relevant to have a look at the total value for each order line. You can check that by simply multiplying the two columns in a new column:

1select
2    id
3  , order_id
4  , product
5  , quantity
6  , price
7  , quantity * price as total_value
8from
9    {{raw.e_commerce_sample.webshop_order_line}}

Earlier, when you added the word ‘Customer’ to a new column, you had to use single quotation marks. That’s because you were adding a text value (a string), not referring to a column in the table.

But in this case, you’re working with existing columns — for example, multiplying quantity by price_per_unit. Since you're using column names and doing a calculation, there's no need for quotation marks.

So the query result would look like this:

You can use symbols like +, -, *, % as you would in Excel as well as parentheses ( ) to calculate across values in a row in SQL. You can also add your own values — for example, you can multiply a number by a factor like this:

1select
2    id
3  , order_id
4  , product
5  , quantity
6  , price
7  , quantity * price as total_value
8  , quantity * price * 1.25 as total_value_incl_VAT
9from
10    {{raw.e_commerce_sample.webshop_order_line}}

‍The result of this query should look like this:

Exercise 6: Calculate the total value for each line if one more unit was sold

Say you have a hypothesis at your e-commerce business that you could easily sell one more of each unit, and that you’d like to see how that would influence the total value for each line in the orders. Try to add one more unit to the sold quantity of each order line and then multiply it with the sales to get the new Optimistic scenario total value. If you run your query, it should give a result like this:

Another simple way to solve the exercise above would be to write a query like this:

1select
2    id
3  , order_id
4  , product
5  , quantity
6  , price
7  , (quantity + 1) * price as optimistic_scenario_total_value
8from
9    {{raw.e_commerce_sample.webshop_order_line}}