Count Rows per Symbol with a Filter

Question

Given a table t with columns sym and price, write a select query to count the number of rows where price > 150.0, for each symbol.

For example:

t:([] sym:`A`B`A`C`B`A; price:120.5 151.2 175.0 160.4 149.9 180.3)

Solution

select numRows:count i by sym from t where price>150.0

Explanation

The where clause filters the table down to only the rows with price > 150.0 before anything else happens — grouping and aggregation only ever see the rows that survive the filter.

by sym then splits those remaining rows into one group per distinct symbol, and count i counts how many row indexes (i) fall into each group — in other words, how many rows passed the filter for that symbol.

The result is a keyed table, with one row per sym and a numRows column holding that symbol's count. Symbols with zero qualifying rows simply don't appear in the result at all, since there's no group for them to belong to.