Solved

# Query:  annualized growth rate percentage

Posted on 2014-04-12
383 Views
Hello Experts,

I am not sure how to go about building query for an annual growth rate percentage.

I have 2 devices that I would need to have a growth rate for each.  Below is the example.  The data only start from 2013-09 and will continue to add months.

Any ideas on how I can build my query?  1st I am not sure how it should be calculated, then I would need to write it in SQL.

``````yr_mo	model_name     inservice _totals
2013-09	CGB 1425	       200
2013-10	CGB 1425	       300
2013-11	CGB 1425	       400
2013-12	CGB 1425	       1000
2014-01	CGB 1425	       1200
2014-02	CGB 1425	       1240
2014-03	CGB 1425	       1300
``````

Oracle 8

Thanks!
0
Question by:Maliki Hassani
• 5
• 2
• 2
• +1

LVL 142

Expert Comment

ID: 39996210
can you explain in layman terms what "formula" you would expect, respectively what result(s) you want to get?
0

LVL 31

Assisted Solution

awking00 earned 166 total points
ID: 39996286
I'm not exactly sure what you're looking for, but the following would show the percentage change from month to month and year-to-date for each model_name:
select yr_mo, model_name, inservice_totals,
round((inservice_totals - lag(inservice_totals) over (partition by model_name order by yr_mo))/
lag(inservice_totals) over (partition by model_name order by yr_mo) * 100,2) mthly_growth,
round((inservice_totals - first_value(inservice_totals) over (partition by model_name order by yr_mo))/first_value(inservice_totals) over (partition by model_name order by yr_mo) * 100,2) ytd_growth
from yourtable;
0

Author Comment

ID: 39996295
(New-old)/old * 100
(1230-500) ÷ 500 × 100 = 146%

I see there are other ways of calculating growrh rate but i think this is the most simple.
0

Author Comment

ID: 39996303
Awking.  Thank you I will review and see if i have any questions.
0

Author Comment

ID: 39996361
my apologies..  i forgot I am using MySQL server.  any way you can convert that to. mysql. iYou did answer the question for me though, regardless.
0

Author Comment

ID: 39996369
> Script lines: 1-5 --------------------------
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'over (partition by model_name order by yr_mo))/
lag(inservice_totals) over (part' at line 2
0

LVL 31

Expert Comment

ID: 39996405
Too bad that MySQL doesn't support any analytic functions. You could perhaps use a self join (i.e using aliases t1 and t2 for yourtable) where the model_names are equal and the yr_mo is different by one, then join this to a subquery (aliased as t3) that gets the minimum inservice_totals (i.e. select model_name, min(inservice_totals) as mintot group by model_name) to get the starting inservice_totals value. The monthly percentage growth could then determined by (t2.inservice_totals - t1.inservice_totals)/t1.inservice_totals * 100 and the ytd percentage growth could then be detmined by (t1.inservice_totals - t3.mintot)/t3.mintot * 100. This would, of course, require some manipulation of the yr_mo so the math could be done to determine the difference of 1.
0

LVL 16

Assisted Solution

Wasim Akram Shaik earned 167 total points
ID: 39996461
after seeing awking comment, analytical functions will not work in mysql, tried out a bit in sql for one model and got the result using dual table..

also found out the if we remove " from dual" word from the below query,. this should work in mysql too..

. not sure if this could help.. had given it a try though

SELECT   (  (SELECT inservice_totals
FROM tab1 t
WHERE TO_CHAR (TO_DATE (yr_mo, 'YYYY MM'), 'YYYY MM') IN (
SELECT TO_CHAR (MAX (TO_DATE (yr_mo, 'YYYY MM')),
'YYYY MM'
)
FROM tab1)
- (SELECT inservice_totals
FROM tab1 t
WHERE TO_CHAR (TO_DATE (yr_mo, 'YYYY MM'), 'YYYY MM') IN (
SELECT TO_CHAR (MIN (TO_DATE (yr_mo, 'YYYY MM')),
'YYYY MM'
)
FROM tab1)
)
/ (SELECT inservice_totals
FROM tab1 t
WHERE TO_CHAR (TO_DATE (yr_mo, 'YYYY MM'), 'YYYY MM') IN (
SELECT TO_CHAR (MIN (TO_DATE (yr_mo, 'YYYY MM')),
'YYYY MM'
)
FROM tab1)
AND model_name = 'ADB 1') AS diff
FROM DUAL
0

LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 167 total points
ID: 39996483
in mysql, I would try this (untested):
``````select t.model_name
, max(case when t.yr_mo = g.min_yr_mo then t._totals end) min_total
, max(case when t.yr_mo = g.max_yr_mo then t_totals end) max_total
, 100 * ( max(case when t.yr_mo = g.max_yr_mo then t_totals end) - max(case when t.yr_mo = g.min_yr_mo then t._totals end)) /
max(case when t.yr_mo = g.min_yr_mo then t_totals end) annual_growth
from (
select min(yr_mo) min_yr_mo
, max(yr_mo) max_yr_mo
, model_name
from yourtable
group by model_name
) g
join yourtable t
on t.model_name  = g.model_name
group by  t.model_name
``````
0

Author Closing Comment

ID: 39996516
thank you all!  Sorry for the confusion in the begining.  I appreciate the help!  I learned several things...
0