What Are the Components of a q Select Query?

Question

Describe the components of a q select query and what each clause does. Given the following table:

trade:([] date:10#.z.d; sym:10?`AAPL`MSFT`GOOG; time:10?23:59:59.999; price:10?500.0; qty:10?1000j)

Write a query that returns the total quantity and average price for each symbol, only for rows where the price is above 200.

Solution

select total_qty:sum qty, avg_price:avg price by sym from trade where price > 200

Explanation

A q select query has a required from clause and three optional clauses, evaluated in a fixed order:

select [columns] [by grouping] from table [where conditions]

from table — the data source. Always required. Can be a table name, a keyed table, or a sub-expression.

where conditions — applied first, before any aggregation. Filters rows; conditions are evaluated left to right, each receiving only the rows that passed the previous condition. All standard q comparisons and boolean operators work here:

where price > 200
where date = .z.d, sym in `AAPL`MSFT

by grouping — groups the remaining rows by one or more columns, similar to SQL's GROUP BY. The result becomes a keyed table, with the by columns as the key:

select avg price by sym from trade       / keyed table: sym → avg price
select avg price by sym, date from trade / keyed by sym × date

select columns — specifies which columns to compute and return. Without a by clause these are simple column expressions. With a by clause they are normally aggregations (such as sum or avg) applied within each group; a column that is not aggregated returns the list of that group's values. Column aliases use colon:

select total_qty:sum qty, avg_price:avg price by sym from trade where price > 200

Evaluation order:

1. from    — locate the table
2. where   — filter rows
3. by      — partition filtered rows into groups
4. select  — compute column expressions (aggregated within each group if by was used)

This is important: where sees the raw column values, not computed select aliases. You cannot filter in where on a column computed in select.

Bare select — omitting all clauses returns the entire table unchanged:

select from trade   / returns the whole table

exec vs select — exec returns lists and atoms instead of a table. Use it when you need a plain list or a single scalar rather than a one-column table:

select sym from trade   / one-column table
exec sym from trade     / plain list: `AAPL`MSFT...
exec first sym from trade  / atom: the first sym, e.g. `AAPL