Column totals
Calculate a total for a numeric column across the rows returned by a select query.
Drizzle ORM · Function
sum
sum creates a SQL aggregate expression that adds all non-null values from a column or expression. Use it inside a select query, and group non-aggregate selected columns when necessary.
How it works
Import sum from drizzle-orm and pass a table column to it inside a select projection. Drizzle emits the database SUM aggregate; when combining the aggregate with ordinary selected columns, add groupBy for those columns.
Syntax
sum(expression)Parameters
| Name | Type | Description |
|---|---|---|
expression | SQL expression / column | The SQL expression or table column whose non-null values should be added. |
Returns
A SQL aggregate expression representing the sum of all non-null values in the supplied expression.
Key points
- sum is one of Drizzle's built-in aggregation helpers.
- Import sum directly from the drizzle-orm package.
- Place sum inside a select projection and execute the query from the source table.
- The helper compiles to the database's SUM aggregate over the chosen column.
- The helper avoids writing a raw sql template for this common aggregate.
Common mistakes
- When selecting aggregate results alongside other columns, include groupBy for the non-aggregate columns.
- Null values are excluded from the calculation.
Example: Sum a column
import { sum } from 'drizzle-orm';
import { users } from './schema';
const result = await db
.select({ total: sum(users.id) })
.from(users);Builds a select query whose total field is the SQL sum of all non-null values in users.id.
When to use it
Grouped aggregation
Produce per-category totals by combining sum with groupBy.
Interview questions
QWhat does Drizzle ORM's sum helper do?
sum is an aggregate helper imported from drizzle-orm. Passing a column such as users.id produces a select expression that sums its non-null values.
Follow-up: How can the same operation be written without the helper? Use sqlsum(${users.id}).mapWith(String) in the selected object.
QWhen should sumDistinct be used instead of sum?
Use sumDistinct when duplicate values must be ignored. It computes the sum of non-null, non-duplicate values in the expression.
Follow-up: What is the regular helper's behavior? sum includes every non-null value, including duplicates.
QHow does sum interact with grouped queries?
Aggregate helpers can be combined with grouping and filtering through groupBy and having, as in SQL. If ordinary columns are selected with the aggregate, group those columns.
Follow-up: What should be checked when the result needs numeric runtime conversion? The documented raw-SQL equivalent maps the value with String, so choose an explicit mapper appropriate to the application's result type.
Related
sumDistinctcountavggroupByhavingsql