Why doesn't my Alasql UDF work with grouped data?

Question

Say I have some cats:

interface Cat {
  name: string
  age: number
  color: string
}

I want to sum the ages of cats of each color. This works as expected in (Ala)SQL:

const sql = `
  SELECT name, sum(age)
  FROM ?
  GROUP BY color
`
alasql(sql, [cats])

But if I write a user-defined sum instead:

import {sum} from 'lodash'
alasql.fn.mySum = function(xs) { sum(xs) }

const sql = `
  SELECT name, mySum(age)
  FROM ?
  GROUP BY color
`
alasql(sql, [cats])

then mySum is called with xs equal to undefined. What am I doing wrong here?

Answer 1

I am not sure if this is exactly what you are looking for but I think it might look something like:

import {sum} from 'lodash'
alasql.fn.mySum = function(xs) { return sum(xs) }
alasql(SELECT name, mySum(age) FROM ? GROUP BY color, [cats])

Maybe you forgot the return keyword in your mySum function.

Answer 2

AlaSQL's aggregate functions are called for each row in the dataset, and they typically have an internal state that gets updated with each call. In your case, you're using Lodash's sum function, which is designed to work on an entire array at once, not on a per-row basis.

To fix your issue, you must redefine mySum to behave like an AlaSQL aggregate function.

• Initialize State: Start by initializing a variable to hold the sum.
• Aggregate Function: Create a function that takes two arguments: the accumulator (the running total) and the current value. Update the accumulator with the current value for each row.
• Finalize Function: Optionally, define a function to finalize the result after all rows are processed.

Example:

alasql.fn.mySum = function(state, age) {
switch(state) {
    case 0: // Initialization
        this.total = 0;
        break;
    case 1: // Row processing
        this.total += age;
        break;
    case 2: // Finalization
        return this.total;
}
};

const sql = `
  SELECT color, mySum(age)
  FROM ?
  GROUP BY color
`;
alasql(sql, [cats]);