SQL – Calculating third column from previous two

I have just started SQL and have some difficulties in terms of thinking in this language.

I have now a task where I need to create a new column in a table with values from previous columns. This calculation is based on few conditions. Is this even possible to do easily in SQL?

Let’s say I have a table called dbo.country_sales where

Select *
From dbo.country

generates

CountryName, Year, Sales
—————————
Argentina, 2015, 10
Argentina, 2017, 22
Florida, 2015, 10
Florida, 2017, 8
Germany, 2015, null
Germany, 2017, null

I need to create fourth column with sales development between 2015 and 2017 (sales in 2017 – sales in 2015) so the table would look like:

CountryName, Year, Sales, Development
—————————
Argentina, 2015, 10, 12
Argentina, 2017, 22, 12
Florida, 2015, 10, -2
Florida, 2017, 8, -2
Germany, 2015, null, null
Germany, 2017, null, null

I know how to make it in the select statement but it calculates only 1 value. Tried to search for some hours but did not find anything simple.

Thanks for help. Lan

Answer

You can use CTEs to precompute values. For example:

with a as (
  select countryname, year, sales from t where year = 2015
),
b as (
  select countryname, year, sales from t where year = 2017
),
c as (
  select a.countryname, b.sales - a.sales as development
  from a
  join b on a.countryname = b.countryname
)
select a.*, c.development
from a
join c on c.countryname = a.countryname
union 
select b.*, c.development
from b
join c on c.countryname = b.countryname
order by countryname, year

Advertisement

Answer