SQL TUTORIAL • LESSON 11, Creating Columns

Making calculations in columns

LESSONS

Introduction to columns Creating a new column and naming it AS something Making calculations in columns Using CASE WHEN as IF statements in SQL

Aggregating Data

Want to try your newly developed SQL skills?

Start with Weld

Creating your own columns in SQL becomes even more useful for business analysis when you include calculations in the columns. In the Order_line table, there is both a quantity of what has been bought as well as a 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}}
10select
11    id
12  , order_id
13  , product
14  , quantity
15  , price
16  , quantity * price as total_value
17from
18    {{raw.e_commerce_sample.webshop_order_line}}

‍

When adding the word 'Customer' to the new column, it needed to be encapsulated by quotation marks to indicate that a new value was being added to the column. In this case, you’re simply referring to existing columns and multiplying them with each other. Therefore, there’s no need to encapsulate this in quotation marks. If you try this query on your own, the result should look something like this:

You can use symbols like +, -, *, % as you would in Excel as well as parentheticals ( ) 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}}

‍

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:

In SQL there are often many means to an end, so you’ll usually be able to get the required result by writing different variations of the same query. A 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_scenaro_total_value
8from
9    {{raw.e_commerce_sample.webshop_order_line}}

‍

Next up

Using CASE WHEN as IF statements in SQL

Go to lesson