QueriesAggregates

Query Aggregates

Use aggregates for counts, sums, and summaries over projection rows.

Basic aggregate query

queries:
  unread_notification_count:
    from: user_notifications
    input:
      user_id: { type: string, required: true }
    where:
      user_id: { eq: input.user_id }
      read:    { eq: false }
    group_by: [user_id]
    aggregate:
      count: { count: notification_id }
    limit: 1

The aggregate: block maps output field names to aggregate functions:

FunctionDescription
countCOUNT(col)
sumSUM(col)
avgAVG(col)
minMIN(col)
maxMAX(col)

Revenue summary

queries:
  show_revenue_summary:
    from: show_attendance
    input:
      show_id: { type: string, required: true }
    where:
      show_id:  { eq: input.show_id }
      refunded: { eq: false }
    group_by: [show_id]
    aggregate:
      total_revenue:  { sum: price_cents }
      ticket_count:   { count: ticket_id }
      average_price:  { avg: price_cents }
    limit: 1

Every aggregate query needs group_by. Without it, the query fails to compile or returns incorrect results.

coalesce_zero

Aggregate results on empty groups return NULL. Use coalesce_zero: true to convert NULL aggregates to 0:

queries:
  unread_notification_count:
    from: user_notifications
    input:
      user_id: { type: string, required: true }
    where:
      user_id: { eq: input.user_id }
      read:    { eq: false }
    group_by: [user_id]
    aggregate:
      count: { count: notification_id }
    coalesce_zero: true
    limit: 1

Without coalesce_zero, a user with no matching rows gets an empty result set, not { count: 0 }. If callers expect a number, enable coalesce_zero.

Aggregate anti-pattern

Warning: Do not use fields: - count: col to compute aggregates. This is silently dropped.

# WRONG — silently ignored
fields:
  - count: notification_id

Always use the top-level aggregate: block with group_by. If every query returns 1 or NULL, this is the most likely cause.

Next steps

Filters & JoinsCaching