Conditional summing (Alasql)

Question

I've recently discovered Alasql, which is already saving me tons of work with data manipulation.

Now I'm trying to aggregate yearly revenues for each customer. I've tried partition by with no success, and I've tried this conditional approach, but it only returns either the 2013 or the 2014 data, not both at the same time:

    var custTable = alasql('SELECT CUSTOMER as customer, 
SUM(REVENUE::NUMBER) AS revenueTotal, SUM(CASE WHEN YEAR = "2013" THEN 
REVENUE::NUMBER END) AS revenue2013,  SUM(CASE WHEN YEAR = "2014" THEN 
REVENUE::NUMBER END) AS revenue2014   FROM ? GROUP BY CUSTOMER', 
[revenueTestData]);

Basically I'm trying to get from this:

{"YEAR":"2013","MONTH":"1","CUSTOMER":"Some Customer","REVENUE":"7533.36"}, {"YEAR":"2014","MONTH":"1","CUSTOMER":"Some Customer","REVENUE":"10000"}

To this:

{"CUSTOMER":"Some Customer","revenueTotal":"17533.36","revenue2013":7533.36","revenue2014":"10000"}

Why does the query only return 2013 OR 2014, but not both? What is the right way to conditionally sum?

(Btw, I couldn't create a new tag for ALASQL, but I think it would come handy to some in the near future - can someone create it?)

Answer 1

Try this:

SUM(CASE WHEN YEAR = "2013" THEN REVENUE::NUMBER ELSE 0 END) AS revenue2013,  
SUM(CASE WHEN YEAR = "2014" THEN REVENUE::NUMBER ELSE 0 END) AS revenue2014