THE INCQL BOOK

9. Summarize and order results

Turn order rows into a grouped, typed summary with IncQL's SQL-familiar query clauses, then collect the deferred result.

Part IV

Query blocks

Chapter 2 of 2

Goal

Group the tutorial orders by status, calculate a row count and amount total for every group, order those summaries by the projected total, and materialize the result through the Session.

Turn rows into a summary relation

The first query-block chapter filtered and projected individual rows. This chapter changes the result's grain: after GROUP BY .status, one output row represents one distinct status rather than one input order. Every non-aggregate field selected by the query must therefore be compatible with the available group keys, while count() and sum(.amount) produce one value for the whole group.

The aggregate helpers are ordinary imported IncQL functions. The checkpoint imports count, sum, and desc from pub::incql; the query vocabulary decides where those expressions are valid and how they contribute to the relational plan. The aliases order_count and total_amount become columns in the projected schema, so the later ORDER BY can refer to .total_amount directly.

This is intentionally SQL-familiar syntax, not an embedded SQL dialect. An IncQL query block requires an explicit SELECT; predicates use Incan equality ==, not SQL assignment-like =; descending order is expressed as desc(.total_amount), not postfix DESC. IncQL also has no HAVING keyword. To filter grouped output, place a second WHERE after SELECT, where the projected aliases are in scope.

With this example, you are describing a deferred LazyFrame[StatusSummary] with three planned output columns—status, order_count, and total_amount—and then asking the Session to collect it. The StatusSummary annotation records the output model you intend to carry. IncQL checks the evolving query schema and the selected aliases, but full field-by-field and type-by-type compatibility validation against that annotated output model remains follow-up work; the annotation alone is not proof of complete model conformance.

01
Grouped queryDescribe the grain, measures, and output order
```
summaries: LazyFrame[StatusSummary] = query {
    FROM orders
    GROUP BY .status # (1)!
    SELECT # (2)!
        .status as status,
        count() as order_count, # (3)!
        sum(.amount) as total_amount,
    ORDER BY desc(.total_amount) # (4)!
}
```
1. Choose the result grain. GROUP BY .status partitions the current orders relation by its status field. After this clause, a selected non-aggregate expression must be compatible with the group keys; this is why the query selects .status alongside aggregate measures.
2. Publish an explicit output schema. SELECT is required rather than implied. Its aliases name the three columns available to following clauses and to the returned carrier: status, order_count, and total_amount.
3. Compute measures with imported functions. count() counts rows in each status group; sum(.amount) totals that group's amount values. Both helpers must be imported from pub::incql. Their aliases make the measures addressable after the projection boundary.
4. Order the projected relation. .total_amount resolves to the alias introduced by the preceding SELECT. desc(...) constructs descending ordering intent; postfix SQL syntax such as .total_amount DESC is not part of the query-block grammar.
Owner
IncQL query vocabulary and relational carrier

Artifact
A deferred LazyFrame[StatusSummary]

Knowable now
Group key, projected aliases, aggregate intent, and descending order
02
Collection boundarySubmit the deferred summary through the Session
```
result = session.collect(summaries)?
```
Owner
Session and the selected DataFusion adapter

Artifact
A materialized grouped result

Knowable now
The query executed successfully and returned three status groups
03
Materialized summaryRead the projected columns and ordered group values

Columns
status, order_count, total_amount

Rows
3 grouped statuses

First group
paid with total 209.75

Grouped result Collected through Session

Result you should see

The preview formatting is backend-facing, but its ordered values should describe these three rows:

status     order_count  total_amount
paid       2            209.75
pending    1             64.00
cancelled  1             42.75

The order is determined by the aggregate alias, not by the alphabetical spelling of status. Because ORDER BY appears after SELECT, it sees the projected schema and can resolve .total_amount; it does not reach backward into an unrelated outer variable.

Open the complete Chapter 9 checkpoint

"""Chapter 9: group and summarize typed data with IncQL query clauses."""

import pub::incql

from domain import Order, StatusSummary
from pub::incql import LazyFrame, Session, SessionError, count, desc, report_session_error, sum


def summarize_statuses(mut session: Session) -> Result[None, SessionError]:
    """Group order rows and collect a typed summary without method chains."""
    orders: LazyFrame[Order] = session.read_csv("tutorial_orders_summary", "orders.csv")?
    summaries: LazyFrame[StatusSummary] = query {
        FROM orders
        GROUP BY .status
        SELECT
            .status as status,
            count() as order_count,
            sum(.amount) as total_amount,
        ORDER BY desc(.total_amount)
    }

    result = session.collect(summaries)?
    println(f"columns: {result.resolved_columns():?}")
    println(f"rows: {result.row_count()}")
    println(result.preview_text())
    return Ok(None)


def main() -> None:
    mut session = Session.default()
    match summarize_statuses(session):
        Ok(_) => println("Chapter 9: grouped query block completed")
        Err(error) => report_session_error("tutorial.chapter_09", error)

Run it from examples/tutorial_book:

incan run src/chapter_09.incn

The checkpoint prints the resolved output columns, a row count of three, and the preview. That collection is the only execution call in the chapter; building the query block itself only appends checked relational intent to the lazy carrier.

For the complete clause inventory, expression rules, and resolution contract, use the Query blocks reference. The tutorial keeps this chapter focused on one grouped path rather than duplicating that catalog.

Try it

After SELECT, add WHERE .total_amount > 50 and run the checkpoint again. This is IncQL's post-projection filter: it uses the projected alias where SQL might use HAVING, leaving the paid and pending groups. Restore the checkpoint before continuing.

Part complete

You can explain how a query block changes row grain, why grouped fields and aggregate measures obey different selection rules, how projected aliases flow into later clauses, and when deferred query intent becomes a materialized result.