Grouping and Having Clauses

groupBy() collapses rows, and having() filters what is left:

(Ticket.query()
    .select('show_id')
    .count('id', alias='sold')
    .groupBy('show_id')
    .having('COUNT(id)', '>', 100))
# SELECT show_id, COUNT(id) AS sold FROM tickets GROUP BY show_id HAVING COUNT(id) > 100

WHERE runs before the grouping, and HAVING runs after, so a filter on a raw column belongs in where() and a filter on an aggregate belongs in having(). The database rejects an aggregate in where(), and Sustained does not check for one.

The examples use the venue booking schema from Getting Started.

Grouping rows

groupBy() takes any number of columns:

Ticket.query().groupBy('show_id')
# SELECT * FROM tickets GROUP BY show_id

Show.query().groupBy('venue_id', 'sold_out')
# SELECT * FROM shows GROUP BY venue_id, sold_out

Pair it with an aggregate from Queries:

(Ticket.query()
    .select('show_id')
    .sum('price', alias='gross')
    .groupBy('show_id')
    .orderBy('gross', 'desc'))
# SELECT show_id, SUM(price) AS gross FROM tickets GROUP BY show_id ORDER BY gross DESC

Filtering groups

having(), andHaving(), and orHaving() chain the same way their where counterparts do, and take the same three arguments:

(Ticket.query()
    .groupBy('show_id')
    .having('COUNT(id)', '>', 100)
    .andHaving('SUM(price)', '>', 5000))
# SELECT * FROM tickets GROUP BY show_id HAVING COUNT(id) > 100 AND SUM(price) > 5000

(Ticket.query()
    .groupBy('show_id')
    .having('AVG(price)', '<', 25)
    .orHaving('MAX(price)', '>', 250))
# SELECT * FROM tickets GROUP BY show_id HAVING AVG(price) < 25 OR MAX(price) > 250

The first condition in a chain must be a plain having(). Starting with andHaving() or orHaving() raises RuntimeError.

Write the aggregate expression itself rather than its alias. Standard SQL evaluates HAVING before the SELECT list exists, so the database rejects having('gross', '>', 5000) even though gross appears in the select list:

# Works everywhere.
(Ticket.query()
    .select('show_id')
    .sum('price', alias='gross')
    .groupBy('show_id')
    .having('SUM(price)', '>', 5000)
)

The having family

The having methods have the same and and or forms as the where family, take the same arguments, and raise the same exceptions.

Method	Arguments
`having`	column, operator, value
`havingIn` / `havingNotIn`	column, list or query
`havingBetween` / `havingNotBetween`	column, low, high
`havingLike` / `havingILike`	column, pattern
`havingNull` / `havingNotNull`	column
`havingExists` / `havingNotExists`	query or callable
`havingRaw`	SQL, parameters

Show.query().groupBy('venue_id').havingIn('venue_id', [1, 2, 3])
# SELECT * FROM shows GROUP BY venue_id HAVING venue_id IN (1, 2, 3)

Venue.query().groupBy('city').havingLike('city', 'Minne%')
# SELECT * FROM venues GROUP BY city HAVING city LIKE 'Minne%'

Ticket.query().groupBy('show_id').havingRaw('SUM(price) % ? = ?', [10, 0])
# SELECT * FROM tickets GROUP BY show_id HAVING (SUM(price) % 10 = 0)

havingILike() follows the same rule as whereILike(): native ILIKE on Postgres and DuckDB, a lowercased comparison everywhere else.

Grouping conditions

Pass a callable to any having method to build a parenthesized group, and call the having methods on the builder it receives:

(Ticket.query()
    .groupBy('show_id')
    .having('COUNT(id)', '>', 50)
    .andHaving(lambda q: (
        q.having('SUM(price)', '<', 1000).orHaving('MAX(price)', '>=', 250)
    ))
)
# SELECT * FROM tickets GROUP BY show_id
# HAVING COUNT(id) > 50 AND (SUM(price) < 1000 OR MAX(price) >= 250)

Groups can nest as deep as the logic needs. having() also accepts a typed predicate built from Model.c and combined with & and | in place of the three arguments, which replaces the nested lambdas with parentheses.

Subtotals and multiple grains

groupByRollup() adds a subtotal for each prefix of the column list, ending in a grand total. The column order defines the hierarchy, so ROLLUP (venue_id, show_id) produces a subtotal for each venue but not for each show on its own:

Ticket.query().select('price').groupByRollup('show_id')
# SELECT price FROM tickets GROUP BY ROLLUP (show_id)

groupByCube() adds a subtotal for every combination of the columns, so column order has no effect and the number of grouping sets doubles with each column you add.

groupByGroupingSets() takes the combinations explicitly, as tuples. An empty tuple is the grand total:

Ticket.query().groupByGroupingSets(('show_id',), ('show_id', 'sold_at'), ())

Rows produced by a subtotal contain NULL in the columns they aggregate over, which is indistinguishable from a real NULL in the data. Engines expose a GROUPING() function to tell them apart, and you can call it through select_func().

Where to go next

You want to	Read
Filter rows before the grouping	Filtering
Build the aggregates being grouped	Queries
Rank inside a group instead of collapsing it	Queries
Run the query	Executing Queries
See every method and what it raises	QueryBuilder reference