Getting Growth Rate Year by Year

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

General SQL Server Forums

New to SQL Server Programming

Getting Growth Rate Year by Year

Author

Topic

gatchie_21
Starting Member

5 Posts

Posted - 2011-01-04 : 03:03:31

Can Anyone help me... I want to create an sql script, where the output is,

1. count of records
2. year
2. year by year growth rate (based on previous year().

how will the sql statement will be? thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 03:47:11

something like

;with cte as
(
select year, num = count(*)
from tbl
group by year
)
select tot = (select count(*) from tbl) ,
cte.year ,
growth = 1.0*(cte.num-cteprev.num)/cteprev.num
from cte
left join cteprev
on cte.year-1 = cteprev.year

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gatchie_21
Starting Member

5 Posts

Posted - 2011-01-04 : 05:44:24

hi nigelrivett.. can't get the correct result.. can you pls explain the query above.. I want sample output below..thank you very much..

example of output:

YEAR count growth rate
2010 20 50%
2009 10 80%
2008 8 62.5%
2007 5

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 05:54:39

Try

;with cte as
(
select year, num = count(*)
from tbl
group by year
)
select tot = cte.num ,
cte.year ,
growth = convert(varchar(6),convert(decimal(5,2),100.0*(cte.num-cteprev.num)/cteprev.num))+'%'
from cte
left join cteprev
on cte.year-1 = cteprev.year

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gatchie_21
Starting Member

5 Posts

Posted - 2011-01-04 : 06:09:20

I tried the sql statement in PUBS database and sales table.
but there is an error on the query.. ERROR: "Invalid column num"
below is the sql.

(select year(ord_date),num = count(*)
from sales
group by year(ord_date))
select tot = (select count(*) from sales ) ,
year(sales.ord_date)
,growth = 100*(num-prev.num)/prev.num
from sales
left join sales prev
on year(sales.ord_date)-1 = prev.ord_date

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 06:20:36

Poor copy - you've missed out and chaged a number of things

try
;with cte as
(
select yr = year(ord_date),num = count(*)
from sales
group by year(ord_date)
)
select tot = cte.num ,
yr ,
growth = 100.0*(cte.num-prev.num)/prev.num
from cte
left join cte prev
on cte.yr-1 = prev.yr

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gatchie_21
Starting Member

5 Posts

Posted - 2011-01-04 : 06:36:04

got it.. many thanks!!

gatchie_21
Starting Member

5 Posts

Posted - 2011-01-06 : 02:42:03

Hi Again nigelrivet,
I tried the pubs database, using two tables, sales and stores.. but the output seems incorrect. Can you pls help me?

with cte as
(
select stor_name,yr = year(ord_date),num = count(*)
from sales left join dbo.stores ON dbo.sales.stor_id = dbo.stores.stor_id
group by stor_name,year(ord_date)
)
select cte.stor_name, tot = cte.num ,
cte.yr ,
growth = convert(varchar(6),convert(decimal(5,2),100.0*(cte.num-prev.num)/prev.num))+'%'
from cte
left join cte prev
on cte.yr-1 = prev.yr
order by cte.stor_name,cte.yr

OUTPUT:

stor_name tot yr growth
Barnum's 1 1993 -66.67%
Barnum's 1 1994 0.00%
Barnum's 1 1994 -50.00%
Barnum's 1 1994 -75.00%
Barnum's 1 1994 -66.67%
Bookbeat 2 1993 -33.33%
Bookbeat 2 1994 100.00%
Bookbeat 2 1994 0.00%
Bookbeat 2 1994 -50.00%
Bookbeat 2 1994 -33.33%
Doc-U-Mat: Quality Laundry and Books 4 1993 33.33%
Doc-U-Mat: Quality Laundry and Books 2 1994 100.00%
Doc-U-Mat: Quality Laundry and Books 2 1994 0.00%
Doc-U-Mat: Quality Laundry and Books 2 1994 -50.00%
Doc-U-Mat: Quality Laundry and Books 2 1994 -33.33%
Eric the Read Books 2 1994 100.00%
Eric the Read Books 2 1994 0.00%
Eric the Read Books 2 1994 -50.00%
Eric the Read Books 2 1994 -33.33%
Fricative Bookshop 3 1993 0.00%
News & Brews 3 1992
News & Brews 1 1994 0.00%
News & Brews 1 1994 -50.00%
News & Brews 1 1994 -75.00%
News & Brews 1 1994 -66.67%

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources