Skip to content
MountSQLiMountSQLI

Group By & Having

groupBy() groups rows so aggregates apply per group. having() filters those groups — like where(), but after aggregation.

Group by a column

Section titled “Group by a column”
const rows = await db
  .from(orders)
  .select("customerId")
  .sum("total", "totalSpent")
  .groupBy("customerId")
  .all();
// → SELECT "customerId", SUM("total") AS "totalSpent" FROM "orders" GROUP BY "customerId"

Multiple group columns

Section titled “Multiple group columns”
db.from(sales)
  .select("region", "product")
  .count("id", "units")
  .groupBy("region", "product")
  .all();

Having

Section titled “Having”

having() filters groups, using the same comparators as where():

db.from(orders)
  .select("customerId")
  .sum("total", "totalSpent")
  .groupBy("customerId")
  .having("totalSpent", ">", 1000)
  .all();

Real-world example

Section titled “Real-world example”

Customers who spent more than $1,000, highest first:

export async function topSpenders() {
  return db
    .from(orders)
    .select("customerId")
    .sum("total", "totalSpent")
    .groupBy("customerId")
    .having("totalSpent", ">", 1000)
    .orderBy("totalSpent", "desc")
    .all();
}

Best practices

Section titled “Best practices”
  • select() the group columns and the aggregates you need.
  • Put row-level filters in where(), group-level in having().
  • Index the group-by column for large tables.

Common mistakes

Section titled “Common mistakes”
  • Selecting a non-grouped, non-aggregated column — undefined per SQL.
  • Using where() to filter an aggregate instead of having().
Section titled “Related”