Count Rows by Group

Question

Given the following table of market prices:

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

Result:

sym price
---------
A   120.5
B   151.2
A   175
C   160.4
B   149.9
A   180.3
C   130
B   155.5
A   99.8
C   201.1

Write a qSQL query to count the number of rows where price > 150.0 for each symbol.

Expected result:

sym| numRows
---| -------
A  | 2
B  | 2
C  | 2

Solution

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

Result:

sym| numRows
---| -------
A  | 2
B  | 2
C  | 2

Explanation

The query:

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

first filters the table using the where clause:

where price>150.0

This keeps only rows whose price is greater than 150.0:

sym price
---------
B   151.2
A   175
C   160.4
A   180.3
B   155.5
C   201.1

Then the query groups the remaining rows by symbol:

by sym

Finally:

numRows:count i

counts the number of rows in each group.

The implicit column i represents the row indices, so count i counts how many rows are present in each grouped result.

The final output shows that each symbol has two rows where the price is greater than 150.0.