MySQL: how to get the percent change of one field compared to the previous
I have a table like this:
year | revenue | organisation
2010 | 83863 | asdf
2011 | 5463 | asdf
2012 | 45345 | asdf
2009 | 32463 | ghjk
2010 | 352667 | ghjk
And I need to search for the latest revenues of all organizations, and up to this point I could build a query like
Select new tablename(v.organisation, MAX(v.year), v.revenue)
from tablename v
where 1=1 group by v.name
But there is one more thing that I need to get, and that is the change in income from the previous year as a percentage.
How can I do both of these functions? Preferred in one request?
thank
+3
source to share
2 answers
Try the following:
SELECT v.organization,
MAX(v.year) AS currentYear,
v.revenue,
((v.revenue -
(SELECT i.revenue
FROM tablename i
WHERE i.organization = v.organization
AND i.year = MAX(v.year) - 1)) /
(SELECT i.revenue
FROM tablename i
WHERE i.organization = v.organization
AND i.year = MAX(v.year) - 1) * 100) AS percentDifference
FROM tablename v
WHERE 1 = 1
GROUP BY v.organization,
v.year;
The sub-selection gets the previous year's earnings measure, then it runs the following formula:
((currentYearRev - lastYearRev)/lastYearRev) * 100
Which should give the percentage difference. I tested it in my environment with your dummy date.
Results:
+--------------+-------------+---------+-------------------+
| organization | currentYear | revenue | percentDifference |
+--------------+-------------+---------+-------------------+
| asdf | 2010 | 83863 | NULL |
| asdf | 2011 | 5463 | -93.4858 |
| asdf | 2012 | 45345 | 730.0384 |
| ghjk | 2009 | 32463 | NULL |
| ghjk | 2010 | 352667 | 986.3660 |
+--------------+-------------+---------+-------------------+
Results showing current income and previous year's income:
+--------------+-------------+---------+-------------------+---------+
| organization | currentYear | revenue | percentDifference | lastRev |
+--------------+-------------+---------+-------------------+---------+
| asdf | 2010 | 83863 | NULL | NULL |
| asdf | 2011 | 5463 | -93.4858 | 83863 |
| asdf | 2012 | 45345 | 730.0384 | 5463 |
| ghjk | 2009 | 32463 | NULL | NULL |
| ghjk | 2010 | 352667 | 986.3660 | 32463 |
+--------------+-------------+---------+-------------------+---------+
+2
source to share
Here's one way ...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(year INT NOT NULL
,revenue INT NOT NULL
,organisation VARCHAR(12) NOT NULL
,PRIMARY KEY(year,organisation)
);
INSERT INTO my_table VALUES
(2010 ,83863 ,'asdf'),
(2011 ,5463 ,'asdf'),
(2012 ,45345 ,'asdf'),
(2009 ,32463 ,'ghjk'),
(2010 ,352667 ,'ghjk');
SELECT x.*
, ((x.revenue - z.revenue)/z.revenue) * 100 pct_diff
FROM my_table x
JOIN
( SELECT organisation
, MAX(year) max_year
FROM my_table
GROUP
BY organisation
) y
ON y.organisation = x.organisation
AND y.max_year = x.year
LEFT
JOIN my_table z
ON z.organisation = x.organisation
AND z.year = x.year - 1;
+------+---------+--------------+----------+
| year | revenue | organisation | pct_diff |
+------+---------+--------------+----------+
| 2010 | 352667 | ghjk | 986.3660 |
| 2012 | 45345 | asdf | 730.0384 |
+------+---------+--------------+----------+
Here's one (slow) version of the current story ...
SELECT x.*
, ((x.revenue - y.revenue)/y.revenue) * 100 pct_diff
FROM my_table x
LEFT
JOIN my_table y
ON y.organisation = x.organisation
AND y.year = x.year - 1
ORDER
BY organisation
, year;
... I'll post a faster alternative if you get a moment (/ remember how)
+1
source to share