asked on

Percentage increase or decrease

I have a SQL script that displays the total amount purchased for the past 3 years I would like to also display the percent of increase or decrease year to year. Not sure how to go about that.

SELECT        Vendor_ID, Vendor_Name,
sum(case when ([Year]) = 2013 then Purchase_Ammount else 0 end) as Purchase_Ammount_2013,
sum(case when ([Year]) = 2014 then Purchase_Ammount else 0 end) as Purchase_Ammount_2014,
sum(case when ([Year]) = 2015 then Purchase_Ammount else 0 end) as Purchase_Ammount_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING        (Class_ID = 'MAN')

Open in new window

Scott Pletcher

SELECT Vendor_ID, Vendor_Name,
sum(case when ([Year]) = 2013 then Purchase_Ammount else 0 end) as Purchase_Ammount_2013,
sum(case when ([Year]) = 2014 then Purchase_Ammount else 0 end) as Purchase_Ammount_2014,
sum(case when ([Year]) = 2015 then Purchase_Ammount else 0 end) as Purchase_Ammount_2015,
sum(case when ([Year]) = 2014 then Purchase_Ammount else 0 end) * 100 /
nullif(sum(case when ([Year]) = 2013 then Purchase_Ammount else 0 end), 0) as Purchase_Percent_2014,
sum(case when ([Year]) = 2015 then Purchase_Ammount else 0 end) * 100 /
nullif(sum(case when ([Year]) = 2014 then Purchase_Ammount else 0 end), 0) as Purchase_Percent_2015
FROM SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING (Class_ID = 'MAN')

Fred Webb

ASKER

Thanks Scott, but I don't think it is calculating properly , it is not calculating the minus percentage, as you can see in the example below 2014 was $2,145 less than 2013 but it shows a positive 51.5 percent and from 20014 to 2015 it shows a positive 122.5 percent increase but id is only a $513 dollar increase

Qlemo

The Class_ID in GROUP BY and HAVING is misplaced here, it should go into a WHERE only.
Since we are using SUMs multiple times, it is time for ad-hoc views (aka inline views, ...)

select Vendor_ID, Vendor_Name,
  Purchase_Amount_2013,
  Purchase_Amount_2014, (Purchase_Amount_2014-Purchase_Amount_2013)/Purchase_Amount_2013 as Percentage_2013,
  Purchase_Amount_2015, (Purchase_Amount_2013-Purchase_Amount_2015)/Purchase_Amount_2014 as Percentage_2014
from 
(
SELECT        Vendor_ID, Vendor_Name,
sum(case when ([Year]) = 2013 then Purchase_Ammount else 0 end) as Purchase_Ammount_2013,
sum(case when ([Year]) = 2014 then Purchase_Ammount else 0 end) as Purchase_Ammount_2014,
sum(case when ([Year]) = 2015 then Purchase_Ammount else 0 end) as Purchase_Ammount_2015
FROM            SSG_Vendor_Yearly_Totals
where Class_ID = 'MAN'
GROUP BY Vendor_ID, Vendor_Name
) data

Open in new window

Fred Webb

ASKER

Qlemo I am getting the following errors;

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Purchase_Amount_2013'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Purchase_Amount_2014'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Purchase_Amount_2014'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Purchase_Amount_2013'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Purchase_Amount_2013'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Purchase_Amount_2015'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Purchase_Amount_2013'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Purchase_Amount_2015'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Purchase_Amount_2014'.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

And you, skull52, had a type in your original question, and I took it over. And I forgot to make it percent instead of a float:

select Vendor_ID, Vendor_Name,
  Purchase_Amount_2013,
  Purchase_Amount_2014, (Purchase_Amount_2014-Purchase_Amount_2013)*100.0/Purchase_Amount_2013 as Percentage_2013,
  Purchase_Amount_2015, (Purchase_Amount_2013-Purchase_Amount_2015)*100.0/Purchase_Amount_2014 as Percentage_2014
from 
(
SELECT        Vendor_ID, Vendor_Name,
sum(case when ([Year]) = 2013 then Purchase_Ammount else 0 end) as Purchase_Amount_2013,
sum(case when ([Year]) = 2014 then Purchase_Ammount else 0 end) as Purchase_Amount_2014,
sum(case when ([Year]) = 2015 then Purchase_Ammount else 0 end) as Purchase_Amount_2015
FROM            SSG_Vendor_Yearly_Totals
where Class_ID = 'MAN'
GROUP BY Vendor_ID, Vendor_Name
) data

Open in new window

Fred Webb

ASKER

As always Scott you came through