You should specify which database you are using, here the SQL Server syntax!
Also, you need to use the good table and column names, I use sample names.
I suggest that you create a view first that gives you the year with the total:
create view vw_year_totals
as
select year_number, sum(value) as year_value
from yourtable
group by year_value
Then, use that view to bring together the years with the previous year:
select c.year_number, c.year_value, p.year_value
, case when p.year_value is null then "N/A" when c.year_value = p.year_value then "0%"
else cast( varchar(5), 100 * ( 1.00 - (c.year_value / ( c.year_value - p.year_value)) )) + '%' end as percent_increase
from vw_year_totals c
left join vw_year_totals p
on c.year_number = p.year_number + 1
That should return the data. Hopefully I didn't write any syntax errors or formula errors (typed directly here), but you should have got the idea
Main Topics
Browse All Topics
by: jrb1Posted on 2005-08-18 at 09:58:47ID: 14702300
What kind of data? And what database? In Oracle, this will work...should be similar in other DBs.
key date amount
a1 1/1/2004 100
a2 1/2/2004 200
b1 1/1/2005 500
b2 3/1/2005 50
You could:
select (sum(a.amount) - sum(b.amount)) / sum(b.amount) * 100
from yourtable a
, yourtable b
where to_char(a.date,'yyyy') = to_char(sysdate,'yyyy')
and to_char(b.date,'yyyy') = to_char(add_months(sysdate