Aggregations

Use Drizzle's aggregation functions with db.select() for counting and numeric computations.

Count

import { drizzle } from '@superapp/db'
import { count } from 'drizzle-orm'
import * as schema from './generated/schema'

const db = drizzle({ connection, token, schema })

const [result] = await db.select({ count: count() })
  .from(schema.orders)
// result.count: number

Count with Filter

import { count, eq } from 'drizzle-orm'

const [result] = await db.select({ count: count() })
  .from(schema.orders)
  .where(eq(schema.orders.status, 'active'))

Count by Group

const statusCounts = await db.select({
  status: schema.orders.status,
  count: count(),
}).from(schema.orders)
  .groupBy(schema.orders.status)
// [{ status: 'active', count: 42 }, { status: 'pending', count: 15 }]

Sum

import { sum } from 'drizzle-orm'

const [result] = await db.select({
  total: sum(schema.orders.amount),
}).from(schema.orders)
// result.total: string (numeric aggregations return strings)

Average

import { avg } from 'drizzle-orm'

const [result] = await db.select({
  average: avg(schema.orders.amount),
}).from(schema.orders)

Min and Max

import { min, max } from 'drizzle-orm'

const [result] = await db.select({
  minimum: min(schema.orders.amount),
  maximum: max(schema.orders.amount),
}).from(schema.orders)

Multiple Aggregations

import { sum, avg, min, max, eq } from 'drizzle-orm'

const [stats] = await db.select({
  total: sum(schema.orders.amount),
  average: avg(schema.orders.amount),
  minimum: min(schema.orders.amount),
  maximum: max(schema.orders.amount),
}).from(schema.orders)
  .where(eq(schema.orders.status, 'active'))

Group By

import { sum, avg } from 'drizzle-orm'

const byStatus = await db.select({
  status: schema.orders.status,
  total: sum(schema.orders.amount),
  average: avg(schema.orders.amount),
}).from(schema.orders)
  .groupBy(schema.orders.status)
// [{ status: 'active', total: '15000', average: '357.14' }, ...]

Group By with Filter

import { sum, inArray } from 'drizzle-orm'

const byCustomer = await db.select({
  customerId: schema.orders.customerId,
  total: sum(schema.orders.amount),
}).from(schema.orders)
  .where(inArray(schema.orders.status, ['active', 'shipped']))
  .groupBy(schema.orders.customerId)

On this page