MySQL: How to get the percentage of change of one field compared to a previous one

Question

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 all organisations' latest revenues 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's still another piece that I need to get, and that's the change of revenue compared to previous year in percent.

How could I do both of those functionalities? Preferably in one query?

Thanks

Answer 1

Try this:

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-select gets the revenue figure from the previous year then it executes 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 the current revenue and the previous years revenue:

+--------------+-------------+---------+-------------------+---------+
| 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 |
+--------------+-------------+---------+-------------------+---------+

Answer 2

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 a running history...

 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 get a moment(/remember how)