SQL tutorial •
Lesson
11
,
Creating Columns

Making calculations in columns

Creating your own columns in SQL becomes even more useful for business analysis when you include calculations in the columns. In the <span class="code">Order_line</span> 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:

When adding the word <span class="code">'Customer'</span> 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 <span class="code">+</span>, <span class="code">-</span>, <span class="code">*</span>, <span class="code">%</span> as you would in Excel as well as parentheticals <span class="code">( )</span> 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:

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:

Next up:
Using CASE WHEN as IF statements in SQL