Column averages
Compute a scalar average for a numeric table column in a select query.
Drizzle ORM · Function
avg
avg is Drizzle ORM’s aggregate helper for calculating the arithmetic mean of all non-null values in a selected expression. Use it in core select queries, and group non-aggregated columns when they appear alongside the average.
How it works
The avg helper creates an SQL AVG aggregate for a selected expression. Pass it a table column or another SQL expression, and alias the selected result when a descriptive output name is useful.
Syntax
avg(expression)Parameters
| Name | Type | Description |
|---|---|---|
expression | SQL expression | The column or SQL expression whose non-null values are averaged. |
Returns
A selected SQL aggregate representing the arithmetic mean of all non-null values in the expression.
Key points
- avg computes the arithmetic mean while ignoring null inputs.
- The helper avoids writing a raw SQL template for this common aggregate.
- Import avg directly from the drizzle-orm package.
- Place avg inside the selected-field object and provide the target column as its argument.
- The helper corresponds to a raw avg SQL expression mapped to a string result.
Common mistakes
- When combining avg with ordinary columns in one query, add groupBy for the non-aggregated columns.
Example: Calculate an average
import { avg } from "drizzle-orm";
const result = await db
.select({ averageId: avg(users.id) })
.from(users);Selects the arithmetic mean of all non-null values in users.id.
When to use it
Grouped statistics
Summarize grouped records by selecting avg alongside grouping columns.
Expression-based averages
Calculate an average over a custom SQL expression when the built-in helper does not express the required expression.
Interview questions
QWhat does avg do?
avg is a wrapped SQL aggregate helper imported from drizzle-orm. It returns the arithmetic mean of all non-null values in the supplied expression.
Follow-up: How would you express the same operation without the helper? Use sqlavg(${users.id}).mapWith(String) in the selected field.
QHow is avg used in a Drizzle select?
Use avg(column) inside db.select, such as db.select({ value: avg(users.id) }).from(users). The selected object gives the aggregate a usable result key.
Follow-up: How would you calculate a distinct average? Replace avg with avgDistinct, which emits AVG(DISTINCT expression).
QWhat should you consider when combining avg with other selected columns?
If a query selects avg together with other columns, group the non-aggregated columns with .groupBy(). This follows the SQL grouping rule for aggregate queries.
Follow-up: Where should an aggregate be placed when using relational queries? The documentation says aggregations are not supported in extras, so use core queries.
Related
avgDistinctcountsumgroupByhavingsql