percentage growth - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

percentage growth

Author

Topic

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-09-15 : 05:27:05

drop table Test

;with cte as (
select 'Capcom' as Comapny
, 320 as Total
, 2012 as Year
union all
select 'Capcom' as Comapny
, 520 as Total
, 2013 as Year
union all
select 'Capcom' as Comapny
, 750 as Total
, 2014 as Year
union all
select 'Konami' as Comapny
, 220 as Total
, 2012 as Year
union all
select 'Konami' as Comapny
, 420 as Total
, 2013 as Year
union all
select 'Konami' as Comapny
, 820 as Total
, 2014 as Year
union all
select 'Rare' as Comapny
,20 as Total
, 2012 as Year
union all
select 'Rare' as Comapny
, 150 as Total
, 2013 as Year
union all
select 'Rare' as Comapny
, 50 as Total
, 2014 as Year
union all
select 'Namco' as Comapny
,220 as Total
, 2012 as Year
union all
select 'Namco' as Comapny
, 350 as Total
, 2013 as Year
union all
select 'Namco' as Comapny
, 345 as Total
, 2014 as Year
union all
select 'UbiSoft' as Comapny
,80 as Total
, 2012 as Year
union all
select 'UbiSoft' as Comapny
, 120 as Total
, 2013 as Year
union all
select 'UbiSoft' as Comapny
, 110 as Total
, 2014 as Year

)
select *
into Test
from cte

select * from Test

how to identify the top 3 companines who have had the largest percentage growth over the last two years?

Thank you

Arun Babu N
Starting Member

26 Posts

Posted - 2014-09-15 : 06:07:03

Select top 3 Comapny,RunningTotal 'Growth over the last two years'
from (
Select distinct Comapny,Sum(total) over(partition by Comapny order by year desc)'RunningTotal' ,
dense_rank() over(partition by Comapny order by year desc)'Ranks' from dbo.Test1
)as test
where ranks =2
order by RunningTotal desc

Arun Babu N

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-09-15 : 06:25:36

i keep getting error :-

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'order'.

Can you post the output of your query please.

And thank you

Arun Babu N
Starting Member

26 Posts

Posted - 2014-09-15 : 07:53:28

which version u r using?

Comapny Growth over the last two years
Capcom 1270
Konami 1240
Namco 695

in 2008 r2 try the below code,

Select top 3 Comapny,RunningTotal 'Growth over the last two years'
from (
Select distinct Comapny, (Select Sum(total) from dbo.Test1 t1
where t1.Comapny = t2.Comapny
and t1.Year >= t2.Year
) 'RunningTotal' ,
dense_rank() over(partition by Comapny order by year desc)'Ranks' from dbo.Test1 t2
)as test
where ranks =2
order by RunningTotal desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources