SQL - Calculating third column from previous two

Question

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

Incidentally, Florida is not a country, the last I checked ;) — Gordon Linoff, Jun 17 '19 at 17:35
Did you intend to have a value of 12 in Argentina 2015 Development? Or do you really want that only in the 2017 record? — Dave Skender, Jun 17 '19 at 18:32
In reallity I will calculate CAGR, but this is just an example. The CAGR will make sense only for the last year in selected period. I can put the same CAGR or null in the other than final years. — Lanparties, Jun 17 '19 at 18:55

score 3 · Answer 1 · answered Jun 17 '19 at 17:34

3

One simple method uses window functions and conditional aggregation:

select c.*,
       sum(case when year = 2017 then sales
                when year = 2015 then - sales
           end) over (partition by country) as diff_2017_2015
from dbo.country c
where year in (2015, 2017);

answered Jun 17 '19 at 17:34

Gordon Linoff

1,242,037
58
646
786

Yeah, this query is way shorter than mine. – The Impaler Jun 17 '19 at 17:58

score 1 · Accepted Answer · answered Jun 17 '19 at 17:35

1

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

answered Jun 17 '19 at 17:35

The Impaler

45,731
9
39
76

Thanks for this one. I needed to do some CAGR calculation via `POWER` so this was a bit better way than first suggested solution. Unfortunately this one takes a bit more time because of joins I guess. – Lanparties Jun 18 '19 at 10:40

Dave Skender · Answer 3 · 2019-06-17T18:44:02.220

For a slightly more exotic solution that might give you more flexibility for future use, you can create a helper function.

CREATE FUNCTION dbo.udf_SalesDiff(
  @CountryName  VARCHAR(50)
 ,@LookbackYear INT
 ,@CurrentSales MONEY
)
RETURNS MONEY
BEGIN

  DECLARE @Diff MONEY

  SELECT @Diff = @CurrentSales - [Sales]
  FROM dbo.CountrySales
  WHERE [CountryName] = @CountryName AND [Year] = @LookbackYear

  RETURN @Diff

END

Where you can use it like this:

SELECT 
  [CountryName]
 ,[Year]
 ,dbo.udf_SalesDiff([CountryName],2015,[Sales]) AS Development
FROM dbo.CountrySales
WHERE [Year]=2017

and will produce:

CountryName | Year | Development
--------------------------------
Argentina   | 2017 | 12
Florida     | 2017 | -2
Germany     | 2017 | NULL

My assumption is that these records are unique on CountryName and Year. To ensure, I'd also recommend `CREATE UNIQUE INDEX uidx_CountrySales ON dbo.CountrySales(CountryName,Year)` Otherwise, you'll have to use a SUM in the function and base query to aggregate. — Dave Skender, Jun 17 '19 at 18:39

SQL - Calculating third column from previous two

3 Answers3