07 - Aggregations & Ordering

Aggregate Functions

Drizzle provides aggregate functions that work like their SQL counterparts. Import them from drizzle-orm:

import { count, sum, avg, min, max } from "drizzle-orm";

Count

Count rows in a table:

const [result] = await db.select({ total: count() }).from(users);
console.log(result.total);  // 42

// Count with a condition
const [active] = await db.select({ total: count() }).from(users)
  .where(eq(users.active, true));
console.log(active.total);  // 35

// Count a specific column (ignores NULLs)
const [withEmail] = await db.select({ total: count(users.email) }).from(users);

Sum, Avg, Min, Max

import { sum, avg, min, max } from "drizzle-orm";

// Total revenue
const [revenue] = await db.select({ total: sum(orders.amount) }).from(orders);
console.log(revenue.total);  // "15230.50"

// Average order value
const [average] = await db.select({ avg: avg(orders.amount) }).from(orders);
console.log(average.avg);  // "76.15"

// Price range
const [range] = await db.select({
  cheapest: min(products.price),
  mostExpensive: max(products.price)
}).from(products);
console.log(range.cheapest, range.mostExpensive);  // 5, 299

Note: sum and avg return strings, not numbers. PostgreSQL returns numeric types as strings to avoid precision loss. Cast them if needed: Number(revenue.total).

Group By

Group rows and aggregate per group:

// Count users per country
const stats = await db.select({
  country: users.country,
  total: count()
}).from(users)
  .groupBy(users.country);
// [{ country: "US", total: 15 }, { country: "IN", total: 8 }, ...]

// Total sales per product
const sales = await db.select({
  productId: orders.productId,
  totalSold: sum(orders.quantity),
  revenue: sum(orders.amount)
}).from(orders)
  .groupBy(orders.productId);

You must include every non-aggregated column in groupBy. This is a SQL rule, not a Drizzle rule:

// ❌ Error: city must be in groupBy
await db.select({
  country: users.country,
  city: users.city,
  total: count()
}).from(users)
  .groupBy(users.country);

// ✅ Include all non-aggregated columns
await db.select({
  country: users.country,
  city: users.city,
  total: count()
}).from(users)
  .groupBy(users.country, users.city);

Having

having filters groups after aggregation. Use it instead of where when filtering on aggregate results:

// Countries with more than 5 users
const popular = await db.select({
  country: users.country,
  total: count()
}).from(users)
  .groupBy(users.country)
  .having(gt(count(), 5));
// [{ country: "US", total: 15 }, { country: "IN", total: 8 }]

where filters rows before grouping. having filters groups after aggregation:

// Active users per country, only countries with 3+ active users
const result = await db.select({
  country: users.country,
  activeCount: count()
}).from(users)
  .where(eq(users.active, true))       // filter rows first
  .groupBy(users.country)
  .having(gte(count(), 3));             // then filter groups

Order By and Limit

Sort and paginate results:

import { asc, desc } from "drizzle-orm";

// Newest users first
const recent = await db.select().from(users)
  .orderBy(desc(users.createdAt));

// Alphabetical
const sorted = await db.select().from(users)
  .orderBy(asc(users.name));

// Multiple sort columns
const ranked = await db.select().from(users)
  .orderBy(desc(users.score), asc(users.name));

// Pagination
const page2 = await db.select().from(users)
  .orderBy(asc(users.id))
  .limit(10)
  .offset(10);  // skip first 10, get next 10

Aggregations with Joins

Combine aggregations with joins for real-world queries:

// Count posts per user
const postCounts = await db.select({
  userName: users.name,
  postCount: count(posts.id)
}).from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id, users.name);
// [{ userName: "Alice", postCount: 5 }, { userName: "Bob", postCount: 2 }]

// Average order value per customer
const avgOrders = await db.select({
  customerName: customers.name,
  avgOrder: avg(orders.amount),
  totalOrders: count(orders.id)
}).from(customers)
  .leftJoin(orders, eq(customers.id, orders.customerId))
  .groupBy(customers.id, customers.name)
  .having(gt(count(orders.id), 0));

Key Takeaways

Aggregate functions: count(), sum(), avg(), min(), max(), all imported from drizzle-orm
count() with no argument counts rows, count(column) ignores NULLs
sum and avg return strings to avoid precision loss
groupBy requires every non-aggregated column in the select
where filters rows before grouping, having filters groups after aggregation
orderBy with asc()/desc() for sorting, .limit() and .offset() for pagination
Aggregations work with joins for queries like "count posts per user"