How to GROUP BY in afl


#1

Hi Experts,

Assume I have a 2D array, with 2 dimensions X and Y. I’d like to do this:
for each X, count how many Ys are non-empty (or met certain condition), and store it in a 1D array with dimension X.

Something like this in SQL: select count(*) from mytable where condition=true group by X

Thanks

-Yushu


#2

AFL% aggregate(ARRAY, count(), X)
AFL% aggregate(filter(ARRAY, condition), count(
), X)


#3

Just extending Alex’s answer …

If you have a query like this:

SELECT COUNT(*) 
   FROM Something S 
 WHERE S.Some_Part_of_It _expr_ :Const
 GROUP BY S.Another_Part;

The corresponding AQL is identical. The AFL though, depends on nesting SciDB operators.

aggregate ( 
  filter ( 
    Something,
    Some_Part_of_It _expr_ :Const 
  ),
  count(*),
  Another_Part
);

AFL is a functional language. You can nest expressions that produce arrays within each other. Anywhere you see “ARRAY”, you can put another AFL expression.