MySQL - Cumulative SUM or Running SUM in MySQL

Interesting problem I had to solve today. I needed to create a running sum to report YTD values.
One option is to go for cursors. I wanted something more dynamic than creating a stored procedure...
An additional issue I had, is that for every change of country, I needed to re-initialize the totals.
It is here that the user variables of MySQL come to the rescue.

OK. Consider this sales table:

CREATE TABLE `sales` (
 `Country` VARCHAR(2) NOT NULL,
 `Month` VARCHAR(2) NOT NULL,
 `year` SMALLINT NOT NULL,
 `Revenue` DECIMAL(10, 2) NOT NULL) ENGINE=MYISAM;


The data:

INSERT INTO sales
  (Country,
  Month,
  year,
  Revenue)
VALUES("UK", "01", "2009", "10"),
    ("UK", "02", "2009", "10"),
    ("UK", "03", "2009", "10"),
    ("UK", "04", "2009", "10"),
    ("UK", "05", "2009", "10"),
    ("UK", "06", "2009", "10"),
    ("UK", "07", "2009", "10"),
    ("UK", "08", "2009", "10"),
    ("UK", "09", "2009", "10"),
    ("UK", "10", "2009", "10"),
    ("UK", "11", "2009", "10"),
    ("UK", "12", "2009", "10"),
    ("UK", "01", "2010", "11"),
    ("UK", "02", "2010", "11"),
    ("UK", "03", "2010", "11"),
    ("UK", "04", "2010", "11"),
    ("UK", "05", "2010", "11"),
    ("UK", "06", "2010", "11"),
    ("UK", "07", "2010", "11"),
    ("FR", "01", "2009", "12"),
    ("FR", "02", "2009", "12"),
    ("FR", "03", "2009", "12"),
    ("FR", "04", "2009", "12"),
    ("FR", "05", "2009", "12"),
    ("FR", "06", "2009", "12"),
    ("FR", "07", "2009", "12"),
    ("FR", "08", "2009", "12"),
    ("FR", "09", "2009", "12"),
    ("FR", "10", "2009", "12"),
    ("FR", "11", "2009", "12"),
    ("FR", "12", "2009", "12"),
    ("FR", "01", "2010", "13"),
    ("FR", "02", "2010", "13"),
    ("FR", "03", "2010", "13"),
    ("FR", "04", "2010", "13"),
    ("FR", "05", "2010", "13"),
    ("FR", "06", "2010", "13"),
    ("FR", "07", "2010", "13");

And finally the query:


# Last year running sum
SET @vly = 0;
# Actuals running sum
SET @vac = 0;
# Store current country
SET @priorCountry = '';
SELECT IF(@priorCountry != Country, @vly := @vac := 0, 0) AS calcFld0,
  @priorCountry := Country AS Country,
  Month,
  LY,
  AC,
  1 - LY/AC AS MonthVar,
  @vly := @vly + LY AS LY_YTD,
  @vac := @vac + AC AS AC_YTD,
  1 - @vly/@vac AS YtdVar
FROM (  SELECT Country,
    Month,
    SUM(IF(year = 2009, Revenue, 0)) AS LY,
    SUM(IF(year = 2010, Revenue, 0)) AS AC
  FROM sales
  GROUP BY 1, 2
  ORDER BY 1, 2) AS temp

The first column is just a calculation column that needs to be removed but we are closed to have the final report we initially needed:

You can see the gray columns containing the running sums. When we switch from Country FR to Country UK, running sums are re-initialized and will cumulate the revenue from the first month as it should as shown on the yellow line.

Note that all queries where build using MySQL Query Factory.