Aggregations and grouping

The organization dashboard needs a header that reads “23 invoices, $48,200 outstanding,” with a breakdown by status underneath. With the tools you have, db.select().from(invoices) returns 23 rows. But the dashboard doesn’t want the rows: it wants the count of them and the sum of one column. Aggregation closes the gap between the rows a table holds and the numbers a UI reports.

This lesson writes the queries behind that dashboard: invoices per organization, revenue per month, top tags by usage. It’s the same db.select from CRUD basics with a different projection and one new clause, groupBy. Operators like count, sum, and avg import from drizzle-orm, alongside the eq and and you already use.

One mental model carries the whole lesson. An aggregate query collapses many rows into a single number, and groupBy decides which rows collapse together. where filters the rows before the collapse; having filters the collapsed groups after.

Collapsing rows into one number

rows

draft $200

draft $150

sent $900

sent $300

sent $400

paid $1,200

groupBy(status)

one row per group

draft count 2 sum $350

sent count 3 sum $1,600

paid count 1 sum $1,200

Rows fold into one row per group. where thins the left stack before the fold; having thins the right stack after it.

The simplest aggregate doesn’t group at all: it collapses an entire table, scoped to one org, to a single number. “How many invoices does this org have?” is count():

const [{ total }] = await db
  .select({ total: count() })
  .from(invoices)
  .where(eq(invoices.organizationId, orgId));

count() with no argument is SQL’s COUNT(*) , an aggregate function that counts rows. Postgres returns a bigint, but Drizzle casts it to a JS number, so you never see a string here. Watch that detail, because sum and avg behave differently, and the difference is a common source of wrong numbers.

With no groupBy, the whole filtered set collapses to exactly one row, so you destructure with const [{ total }] to pull it out of the array. The projection drives the type: you wrote { total: count() }, so the result is inferred as { total: number }[], the aggregate riding where a column used to.

count() is one of a small family, all imported from drizzle-orm:

Helper

SQL

Returns in TS

Note

count()

count(*)

number

every row

count(col)

count(col)

number

rows where col is non-null

countDistinct(col)

count(distinct col)

number

distinct non-null values

sum(col)

sum(col)

string | null

numeric precision preserved

sumDistinct(col)

sum(distinct col)

string | null

avg(col)

avg(col)

string | null

min(col) / max(col)

min / max

the column's own type

preserves type

The drizzle-orm aggregate helpers, all imported from drizzle-orm. The amber return cells hand you a string and a null over an empty set — the trap the lesson pays off.

One detail in that table matters more than the rest: sum and avg hand you a string, and null over an empty set.

count() and count(col) also differ, and the gap quietly produces wrong numbers. count() counts rows; count(col) counts only rows where col is non-null. On a column that’s always set they agree; on a nullable one they diverge. count(invoices.assignedToId) gives the number of assigned invoices, smaller than count() whenever some invoices have no assignee. That looks like a quirk now, but once a join enters the picture, it’s exactly what makes the count come out right.

One number per group

A single number over the whole table is rarely what a dashboard wants. “How many invoices?” matters less than “how many invoices of each status?” groupBy answers the second: one number per group instead of one for the table.

const byStatus = await db
  .select({ status: invoices.status, total: count() })
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .groupBy(invoices.status);
// → { status: 'draft' | 'sent' | 'paid' | 'void'; total: number }[]

This is the first query whose two parts have to agree, so step through it.

const byStatus = await db
  .select({ status: invoices.status, total: count() })
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .groupBy(invoices.status);
// → { status: 'draft' | 'sent' | 'paid' | 'void'; total: number }[]

The grouped column rides in the projection like any other, but the result is now one row per distinct status, each carrying that status plus its aggregates.

const byStatus = await db
  .select({ status: invoices.status, total: count() })
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .groupBy(invoices.status);
// → { status: 'draft' | 'sent' | 'paid' | 'void'; total: number }[]

The aggregate is computed within each group: one count for draft, one for sent, one for paid.

const byStatus = await db
  .select({ status: invoices.status, total: count() })
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .groupBy(invoices.status);
// → { status: 'draft' | 'sent' | 'paid' | 'void'; total: number }[]

This names the buckets: every distinct status becomes one output row. It carries the rule that trips most people up: every projected column not wrapped in an aggregate, here status, must appear in groupBy.

← Previous 1 / 1 Next →

The reason is mechanical. Collapsing many rows into one leaves a non-aggregated column with no single value: which of a five-row group’s ids should invoices.id become? Postgres won’t guess, so it rejects the query. Either group by the column or aggregate it.

Watch video

Database Star walks GROUP BY from one number to one-per-group in 5 minutes, including the “every selected column must be grouped or aggregated” rule shown as a live error.

This is a runtime error, not a compile-time one: Drizzle builds the SQL, and Postgres rejects it on execution, naming the offending column.

Rejected

await db
  .select({ id: invoices.id, status: invoices.status, total: count() })
  .from(invoices)
  .groupBy(invoices.status);
// ✗ runtime error from Postgres:
// column "invoices.id" must appear in the GROUP BY
// clause or be used in an aggregate function

invoices.id is selected but never grouped or aggregated, so Postgres can’t collapse a group into one id.

Fixed

await db
  .select({ status: invoices.status, total: count() })
  .from(invoices)
  .groupBy(invoices.status);

Drop id: a per-status count has no room for a per-row id. If you wanted per-id rows, you didn’t want an aggregate.

Notice where the org filter sat in byStatus: in where, before the groupBy. In the fold figure’s terms, where thins the left stack before the collapse, shrinking the input rather than the output. That is exactly what will set having apart shortly.

Caution

Group by a bucket, not a continuous value. A high-cardinality column like a raw createdAt timestamp gives almost every row its own group, since no two invoices share an exact creation instant, leaving you nearly one output row per input row. Group by a status or a truncated month (date_trunc, shortly), never a raw continuous value.

Grouping across a join

“Invoices per organization” resembles the last query, except the dashboard wants the org’s name, which lives on organizations, not invoices. Pulling a column from another table forces a join, and joins are where group counts quietly go wrong. The shape you want is one row per org carrying its name, invoice count, and outstanding total.

const perOrg = await db
  .select({
    orgId: organizations.id,
    orgName: organizations.name,
    invoiceCount: count(invoices.id),
    outstanding: sum(invoices.amountDue),
  })
  .from(organizations)
  .leftJoin(invoices, eq(invoices.organizationId, organizations.id))
  .groupBy(organizations.id, organizations.name);

const perOrg = await db
  .select({
    orgId: organizations.id,
    orgName: organizations.name,
    invoiceCount: count(invoices.id),
    outstanding: sum(invoices.amountDue),
  })
  .from(organizations)
  .leftJoin(invoices, eq(invoices.organizationId, organizations.id))
  .groupBy(organizations.id, organizations.name);

You want one row per org, so drive from organizations and join invoices in. leftJoin keeps orgs with zero invoices; innerJoin would silently drop them, and “0 invoices” is exactly what a dashboard must show.

const perOrg = await db
  .select({
    orgId: organizations.id,
    orgName: organizations.name,
    invoiceCount: count(invoices.id),
    outstanding: sum(invoices.amountDue),
  })
  .from(organizations)
  .leftJoin(invoices, eq(invoices.organizationId, organizations.id))
  .groupBy(organizations.id, organizations.name);

Group by the org side, never by anything on invoices. Both id and name appear because both are selected and neither is aggregated.

const perOrg = await db
  .select({
    orgId: organizations.id,
    orgName: organizations.name,
    invoiceCount: count(invoices.id),
    outstanding: sum(invoices.amountDue),
  })
  .from(organizations)
  .leftJoin(invoices, eq(invoices.organizationId, organizations.id))
  .groupBy(organizations.id, organizations.name);

Count invoices.id, not count() — the next paragraph shows why.

← Previous 1 / 1 Next →

Now the trap. For an org with no invoices, the left join still emits one row, every invoices.* column padded to null. The two ways to count diverge on that row:

• count(invoices.id) returns 0. It counts non-null id values, and there are none. Correct.
• count() returns 1. The padding row exists, and count() counts rows regardless of contents. Wrong.

So to count the right side of a left join, count a column from that side; padding rows then score zero, not one.

That empty org hides a second gotcha: its outstanding comes back as null, not 0, because sum over zero rows is null in SQL. That null reaches your first customer with no invoices yet, whose dashboard tile reads “$null.” The one-line fix comes a couple of sections from now; for now, just register that sum over nothing is null.

Filtering groups with having

Sometimes you want only the groups that clear a bar: “which orgs owe us more than $1,000?” That’s a filter on a sum, and a sum doesn’t exist until the rows have collapsed. where can’t do it; having can.

const bigDebtors = await db
  .select({ orgId: invoices.organizationId, outstanding: sum(invoices.amountDue) })
  .from(invoices)
  .where(eq(invoices.status, 'sent'))
  .groupBy(invoices.organizationId)
  .having(({ outstanding }) => gt(outstanding, '1000'));

The whole distinction fits in one line:

where reduces the rows going in; having reduces the groups coming out.

On the fold figure, where thins the left stack before the collapse, having thins the right stack after.

The having callback receives your projected fields by the keys you named in select, so you write outstanding instead of restating sum(invoices.amountDue). Name the aggregate once in the projection and reference it everywhere else; that’s also why the sum rides in the select even when you only want the filtered groups.

That leaves a clean rule for where any predicate goes:

• A predicate on a raw column (status = 'sent') belongs in where. It filters before grouping, so it shrinks the work, and the raw column may not survive the fold.
• A predicate on an aggregate (the outstanding sum) must go in having, because where runs first and never sees an aggregate.

The threshold is the string '1000', not the number 1000. amountDue is numeric, so sum returns a string: the same money-as-strings reflex from the schema chapter.

Now practice the distinction. For each predicate below, decide whether it filters rows (where) or groups (having).

Each predicate filters either individual rows or whole groups. Drop it into the clause that can express it. Drag each item into the bucket it belongs to, then press Check.

where Filters rows before grouping

having Filters groups after the count

Only paid invoices

Groups with more than 5 invoices

Invoices created this year

Orgs whose outstanding total exceeds $10k

Assigned invoices only (assignedToId is not null)

Tags used more than 3 times

Check Reset

Conditional aggregates with FILTER

The status-breakdown tile needs three numbers for one org: the paid count, the sent count, and an overall total. One query can compute all three in a single scan.

FILTER (WHERE …) attaches a predicate to an individual aggregate, so each one counts only the rows matching its own condition. Drizzle has no builder method for it, so you drop into the sql template.

const [breakdown] = await db
  .select({
    total: count(),
    paid: sql<number>`count(*) filter (where ${invoices.status} = 'paid')`,
    outstanding: sql<string>`coalesce(sum(${invoices.amountDue}) filter (where ${invoices.status} = 'sent'), 0)`,
  })
  .from(invoices)
  .where(eq(invoices.organizationId, orgId));

const [breakdown] = await db
  .select({
    total: count(),
    paid: sql<number>`count(*) filter (where ${invoices.status} = 'paid')`,
    outstanding: sql<string>`coalesce(sum(${invoices.amountDue}) filter (where ${invoices.status} = 'sent'), 0)`,
  })
  .from(invoices)
  .where(eq(invoices.organizationId, orgId));

Because sql<number> is a claim TypeScript can’t verify, keep it honest: a wrong type surfaces at runtime, not compile time.

The coalesce here fixes the empty-sum bug from the join section, and at the database, where no downstream caller can forget to. You’ll reach for it on essentially every dashboard sum.

Note

The older way to write conditional aggregates was sum(case when status = 'sent' then amount_due else 0 end). FILTER (WHERE …) does the same job, reads cleaner, and is the 2026 default; you’ll see the CASE form in older codebases but don’t need to write it.

One row per group: distinctOn

Sometimes you want a whole row per group, not a number, like “the latest invoice for each org”: one representative row per org rather than a count.

Two methods sound alike, so pick deliberately:

• db.selectDistinct(...) is the plain SQL SELECT DISTINCT: identical rows collapse to one.
• db.selectDistinctOn([col]) compiles to Postgres’ DISTINCT ON : keep the first row per distinct col, where “first” is whatever orderBy says.

The second gives you “latest invoice per org”:

const latestPerOrg = await db
  .selectDistinctOn([invoices.organizationId])
  .from(invoices)
  .orderBy(invoices.organizationId, desc(invoices.createdAt));

The distinctOn column must be the leading orderBy key; the next orderBy key decides which row wins within each group.

So organizationId leads and desc(createdAt) breaks the tie, picking the newest invoice per org. Flip that order or drop createdAt, and Postgres either errors or returns an arbitrary row per group, a bug that passes every test until the data grows enough to expose it.

For the top three per group or any real ranking, you need the window functions covered in the subqueries and CTEs lesson.

Common dashboard queries

Monthly revenue is a query you’ll write constantly, and it leans on a Postgres function to build its buckets.

A month isn’t a column you have. createdAt is a precise timestamp, and grouping by it raw makes a separate group per invoice (the high-cardinality trap from earlier). date_trunc('month', …) flattens every timestamp to the first instant of its month, collapsing all of January’s invoices to one group key.

const monthExpr = sql<string>`date_trunc('month', ${invoices.createdAt})`;

const monthlyRevenue = await db
  .select({
    month: monthExpr,
    revenue: sql<string>`coalesce(sum(${invoices.amountDue}), '0')`,
  })
  .from(invoices)
  .where(and(eq(invoices.organizationId, orgId), eq(invoices.status, 'paid')))
  .groupBy(monthExpr)
  .orderBy(monthExpr);

That date_trunc expression has to appear in select, groupBy, and orderBy, and Drizzle has no positional shortcut like SQL’s GROUP BY 1. Extract it to a const whenever an expression repeats across clauses: write it once, reference it three times.

sum and avg over a numeric column arrive in TypeScript as a string, like every numeric column since the schema chapter.

The string protects precision: JavaScript’s number is an IEEE-754 float that can’t represent every cent exactly, so float math on money corrupts totals into off-by-a-penny bugs. The rule: format the string for display, never parseFloat it for money math.

The money bug

const withTax = parseFloat(row.revenue) * 1.2;

parseFloat drags the exact decimal into a float, and the multiply compounds the error — wrong money. This is what the numeric-to-string design exists to prevent.

Exact in SQL, format in JS

const formatted = new Intl.NumberFormat('en-US', {
  style: 'currency',
  currency: 'USD',
}).format(Number(row.revenue));

Do the math in Postgres, where numeric stays exact — a * 1.2 for tax belongs in the query, where the result still arrives as a string. JavaScript only formats: the string flows to a currency formatter with Number() at that final display step, never to compute with. If you ever must do money math in JS, reach for a decimal library; a display-only dashboard rarely needs one.

Practice: build a dashboard query

Write one end to end: a grouped aggregate across two tables, with the empty-state case handled. The grader checks your result against seeded data.

Return each organization's name, its total invoice count, and its outstanding balance — the sum of amountDue for status = 'sent' invoices only — highest outstanding first. invoiceCount counts every invoice, but outstanding sums only the sent ones, so the status filter rides on the sum alone via FILTER (WHERE …). Keep orgs with no sent invoices, showing '0' for their outstanding (not null).

View schema & seed rows

Schema (Drizzle)

export const organizations = pgTable('organizations', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

export const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  organizationId: integer('organization_id')
    .notNull()
    .references(() => organizations.id),
  amountDue: numeric('amount_due', { precision: 12, scale: 2 }).notNull(),
  status: text('status').notNull(),
});

Seed rows (SQL)

INSERT INTO organizations (id, name) VALUES
  (1, 'Acme'),
  (2, 'Globex'),
  (3, 'Initech');

INSERT INTO invoices (id, organization_id, amount_due, status) VALUES
  (1, 1, '900.00',  'sent'),
  (2, 1, '600.00',  'sent'),
  (3, 1, '1200.00', 'paid'),
  (4, 2, '2000.00', 'sent'),
  (5, 3, '500.00',  'paid');

Run query Feedback Reset

Every query here used db.select, never db.query. The relational API reads a tree of nested objects; aggregates compute over rows, and there’s no first-class way to aggregate through it. The rule: nested rows, reach for db.query; a number about the rows, reach for db.select.

External resources

Drizzle ORM — Aggregations, groupBy & having

orm.drizzle.team

The official select reference: every aggregate helper plus the .groupBy() / .having() methods used throughout this lesson.

PostgreSQL — Aggregate functions, GROUP BY & HAVING

postgresql.org

The authoritative source on the WHERE-before / HAVING-after distinction the whole lesson turns on.

The FILTER clause

modern-sql.com

A focused explainer on FILTER (WHERE …) for conditional aggregates, with the older CASE form side by side.

PostgreSQL DISTINCT ON tutorial

neon.com

A worked one-row-per-group example, including why DISTINCT ON must lead the ORDER BY.