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.

Javascript associative arrays

I was struggling a bit to find something similar to PHP associative arrays that I could use in JavaScript. I like to use them to create hierachies. Here is a quick example of what we can do using them:

var myArray = new Array();
//Create root
myArray["Vehicles"] = new Array();

//Level 1 - create a "Car" level and add first element
myArray["Vehicles"] = {"Cars": ["Audi"]};

// Add children to level 2;
myArray["Vehicles"]["Cars"].push("GM");
myArray["Vehicles"]["Cars"].push("Mercedes");
myArray["Vehicles"]["Cars"].push("Porsche");

// Add children to level 1
myArray["Vehicles"]["Trucks"] = new Array();
// Add children to level 2
myArray["Vehicles"]["Trucks"] = [["Iveco"], ["Volvo"]];
//Add child level 0
myArray["Parts & Accessories"] = new Array();
//Add children level 1 & 2
myArray["Parts & Accessories"]["Wheels"] = [["5 Zingen"], ["AMG"], ["HP Design"]];
myArray["Parts & Accessories"]["BullBars"] = [["16x12"], ["16x18"]];

for (key0 in myArray){
    document.write("<ul><li>" + key0 + "</li><ul>");
    for (key1 in myArray[key0]){
        document.write("<ul><li>" + key1 + "</li>\n<ul>");
        for (i=0;i < myArray[key0][key1].length;i++){
            document.write("<li>" + myArray[key0][key1][i] + "</li>");
        }
        document.write("</ul></ul>");
    }
    document.write("</ul></ul>");
}

This version is a little bit simplified as we suppose we know how deep the hierarchy is. You may not know it in advance. You then need to add some tests. Anyway here is the final result of what the script produces:

• Vehicles
  
  
  
  • Cars
    
    
    
    
    • Audi
    • GM
    • Mercedes
    • Porsche
  • Trucks
    
    
    
    
    • Iveco
    • Volvo

• Parts & Accessories
  
  
  
  
  • Wheels
    
    
    
    • 5 Zingen
    • AMG
    • HP Design
  • BullBars
    
    
    
    • 16x12
    • 16x18

As you can see, JavaScript will accept spaces in keys which may be convenient in some cases. I hope it helped someone. Cheers.