CODEWITHSUNDEEP
sqlsql-server
geothachankary
geothachankary

Reputation: 1082

How to get calculate percentage increase or decrease in single sql query?

I have a table with following structure

Event Id    |  Year   
-------------------  
1xxxxx      |  2014  
2xxxxx      |  2014  
3xxxxx      |  2014  
4xxxxx      |  2014  
5xxxxx      |  2014  
6xxxxx      |  2015  
7xxxxx      |  2015  
8xxxxx      |  2015

I need to find the percentage increase of number of events happened in 2015 compared to 2014. I need to find it using a single SQL query. How can I achieve this?

For example if we take the number of events happened in 2014 it is equal to 5 and the same is 3 in 2015. So the percentage increase of events in 2015 compared to 2014 is ((3-5)*100)/5 = -40.0 %.

Upvotes: 1

Views: 2771

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Here is generic statement which is not limited to only 2014 and 2015:

CREATE TABLE test (id INT, year int);

Insert into test values
(1, 2014),
(2, 2014),
(3, 2014),
(4, 2014),
(5, 2014),
(6, 2015),
(7, 2015),
(8, 2015),
(9, 2016)

;with cte as(
  select year y, count(*) c from test
  group by year)
select c1.y, 
       ca.y,
       (c1.c - ca.c)*100.0/ca.c inc,
       (ca.c - c1.c)*100.0/c1.c dec
from cte c1
cross apply(select top 1 * from cte c2 where c2.y < c1.y order by c2.y desc)ca

Output:

y       y       inc                   dec
2015    2014    -40                   66.666666666666
2016    2015    -66.666666666666      200

Fiddle http://sqlfiddle.com/#!6/9e1cf/3

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271211

if I understand correctly, you can do this with conditional aggregation:

select sum(case when year = 2014 then 1 else 0 end) as ev_2014,
       sum(case when year = 2015 then 1 else 0 end) as ev_2015,
       (sum(case when year = 2015 then 100.0 else 0 end) 
        sum(case when year = 2014 then 1.0 end) 
       ) - 100.0 as percent_change       
from table t;

Upvotes: 1

Related Questions