Count unique column values
You need the number of unique IDs, emails, or other column values in a table.
Drizzle ORM · Function
countDistinct
countDistinct is a Drizzle ORM aggregation helper that counts unique, non-duplicate values in an expression. Use it in a select projection to produce a count(distinct ...) SQL aggregate.
How it works
countDistinct(expression) creates a SQL aggregate that counts unique, non-duplicate values from the supplied expression. Use it in a select projection, where Drizzle emits a count(distinct ...) expression and maps the result to a number.
Syntax
countDistinct(expression)Parameters
| Name | Type | Description |
|---|---|---|
expression | SQL expression | The expression whose unique, non-duplicate values are counted. |
Returns
A SQL aggregate expression that counts the non-duplicate values of the supplied expression.
Key points
- Its result is the count of unique values in the supplied expression.
- Import the helper from the main
drizzle-ormpackage. - Use the helper inside a select projection and query the source table with
.from(). - The corresponding SQL uses the database's
count(distinct ...)aggregate. - The same operation can be expressed manually with a SQL template and numeric result mapping.
Common mistakes
- The function counts unique values rather than every row, so repeated values contribute only once.
Example: Count distinct values
import { countDistinct } from 'drizzle-orm';
const result = await db
.select({ value: countDistinct(users.id) })
.from(users);Counts the distinct user IDs and returns the aggregate under the value key.
When to use it
Build aggregate reports
A report requires a distinct aggregate alongside other selected fields.
Use an equivalent SQL expression
You need explicit SQL control instead of the helper abstraction.
Interview questions
QWhat does countDistinct do?
countDistinct is an aggregation helper imported from drizzle-orm. It counts non-duplicate values in the expression passed to it and can be used as a selected field.
Follow-up: How is it represented in SQL? Drizzle emits count(distinct ...) for the selected expression.
QHow do you use it in a query?
Pass the column or other supported expression to countDistinct, then place the result in a select object. The returned row exposes the aggregate through the selected property name.
Follow-up: How would you count unique user IDs? Select { value: countDistinct(users.id) } from the users table.
QWhat SQL does Drizzle generate?
The helper is a typed Drizzle abstraction over the SQL aggregate count(distinct expression). The documentation also shows that the equivalent SQL can be written with the sql template and numeric mapping.
Follow-up: What is the lower-level alternative? Use sql\count(distinct ${users.id})\.mapWith(Number) in the select projection.
Related
countselectDistinctsql