Question about MIN() aggregator use

[agerard]

I've made huge progress with my proj. thanks to your help and library, and am in the home stretch (a slightly earlier version can be seen at http://beta.ciesin.columbia.edu/jbwq/) . But am having a problem with figuring out how to avoid 0 as the value returned from the MIN() aggregator for columns with null values(Nulls seem to be seen as 0, but 0 isn't a useful minimum). What I think I'm really needing is "LEAST()" - i.e something that drops all nulls/0 values before aggregating. There are probably ways to make a custom function but I am really hoping not to have to do that at this point. My current workaround is to use MIN(IFNULL(' + selectedParam + ',10000)) so any nulls clearly won't be minimums...just wondering if there's a better way.

[agershun]

You can write a custom aggregator function like below:

  alasql.aggr.LEAST = function(v,s,stage){
    if(stage == 1) return v;
    if(stage == 2) {
        if(v == null) return s;
        else return Math.min(s,v);;
    }
    return s; 
  };
var res = alasql('SELECT LEAST(a) FROM ?',[data])

[mathiasrw]

Hi @agerard - did this solve your problem?

I have updated the documentation in https://github.com/agershun/alasql/wiki/MIN and https://github.com/agershun/alasql/wiki/Max

[agerard]

Since I had a workaround, I have been chasing other bugs in preparation for alpha release and have not had a chance to try yet, but will do so today. I hope to close this before the end of the day. Thanks again for your prompt and helpful reply - alasql rocks!

[agerard]

It works perfectly. Will you be adding a LEAST aggregator to the library? I suppose I should be (or should have been) checking to see that my version is up to date....

[mathiasrw]

Will you be adding a LEAST aggregator to the library?

This will be added - but I can say when.

[agerard]

Should this be a new issue? I've just realized that AVG and MEDIAN (perhaps STDEV as well?) are also interpreting nulls as zeros, which is not correct. I think I could probably work out custom aggregator fns for the first 2, but am pretty sure STDEV is beyond me. The nulls are in the data - not converted to 0's - so shouldn't the built-in aggregators ignore those?

[mathiasrw]

You are right!

Lets create an issue with each of Them.

Will do when I get to my computer

[agerard]

Thanks!

[agerard]

The odd thing is, as I look more carefully at my code, I AM already at least trying to eliminate nulls beforehand in my query, but perhaps it's not actually doing so?

alasql('SELECT count(' + selectedChem + ') FROM ? WHERE ' + chemString + ' ORDER BY _date', [ resultSet ]);

where selectedChem might be 'w_temp' and chemString would then be 'w_temp is not null'

Trying this against original postGres table gives a different count. So, it appears IS NOT NULL is also not working? I found the syntax workaround (#458) and that fixes it! But, either that shd be fixed, or docs changed....