AggregateFunctionBuilder

Builder for SQL aggregate functions with filtering, ordering, and window function support

AggregateFunctionBuilder

AggregateFunctionBuilder is returned by aggregate function calls from the FunctionModule. It provides methods for adding modifiers like distinct, filter, order by, and over clauses to aggregate functions.

Type Parameters

DB · object · required

The database schema type

TB · keyof DB · required

The table references available in the current query context

O · any · default: "unknown"

The output type of the aggregate function

Methods

as

Returns an aliased version of the function.

as<A extends string>(
  alias: A,
): AliasedAggregateFunctionBuilder<DB, TB, O, A>

alias · string · required

The alias name for the aggregate function result

In addition to slapping as "the_alias" to the end of the SQL, this method also provides strict typing.

Example:

const result = await db
  .selectFrom('person')
  .select(
    (eb) => eb.fn.count<number>('id').as('person_count')
  )
  .executeTakeFirstOrThrow()

// `person_count: number` field exists in the result type.
console.log(result.person_count)

distinct

Adds a distinct clause inside the function.

distinct(): AggregateFunctionBuilder<DB, TB, O>

Example:

const result = await db
  .selectFrom('person')
  .select((eb) =>
    eb.fn.count<number>('first_name').distinct().as('first_name_count')
  )
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select count(distinct "first_name") as "first_name_count"
from "person"

orderBy

Adds an order by clause inside the aggregate function.

orderBy<OE extends OrderByExpression<DB, TB, {}>>(
  expr: OE,
  modifiers?: OrderByModifiers,
): AggregateFunctionBuilder<DB, TB, O>

expr · OrderByExpression · required

Column or expression to order by

modifiers · OrderByModifiers

Optional order modifiers (e.g., asc, desc, nulls handling)

Example:

const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select((eb) =>
    eb.fn.jsonAgg('pet').orderBy('pet.name').as('person_pets')
  )
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select json_agg("pet" order by "pet"."name") as "person_pets"
from "person"
inner join "pet" ON "pet"."owner_id" = "person"."id"

clearOrderBy

Removes all order by clauses from the aggregate function.

clearOrderBy(): AggregateFunctionBuilder<DB, TB, O>

withinGroupOrderBy

Adds a within group clause with a nested order by clause after the function.

withinGroupOrderBy<OE extends OrderByExpression<DB, TB, {}>>(
  expr: OE,
  modifiers?: OrderByModifiers,
): AggregateFunctionBuilder<DB, TB, O>

expr · OrderByExpression · required

Column or expression to order by

modifiers · OrderByModifiers

Optional order modifiers

This is only supported by some dialects like PostgreSQL or MS SQL Server.

Example:

Most frequent person name:

const result = await db
  .selectFrom('person')
  .select((eb) => [
    eb.fn
      .agg<string>('mode')
      .withinGroupOrderBy('person.first_name')
      .as('most_frequent_name')
  ])
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select mode() within group (order by "person"."first_name") as "most_frequent_name"
from "person"

filterWhere

Adds a filter clause with a nested where clause after the function.

filterWhere<
  RE extends ReferenceExpression<DB, TB>,
  VE extends OperandValueExpressionOrList<DB, TB, RE>,
>(
  lhs: RE,
  op: ComparisonOperatorExpression,
  rhs: VE,
): AggregateFunctionBuilder<DB, TB, O>

filterWhere<E extends ExpressionOrFactory<DB, TB, SqlBool>>(
  expression: E,
): AggregateFunctionBuilder<DB, TB, O>

lhs · ReferenceExpression · required

Left-hand side column reference

op · ComparisonOperatorExpression · required

Comparison operator

rhs · OperandValueExpressionOrList · required

Right-hand side value or expression

expression · ExpressionOrFactory

Alternative: full expression or factory function

Example:

Count by gender:

const result = await db
  .selectFrom('person')
  .select((eb) => [
    eb.fn
      .count<number>('id')
      .filterWhere('gender', '=', 'female')
      .as('female_count'),
    eb.fn
      .count<number>('id')
      .filterWhere('gender', '=', 'male')
      .as('male_count'),
    eb.fn
      .count<number>('id')
      .filterWhere('gender', '=', 'other')
      .as('other_count'),
  ])
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select
  count("id") filter(where "gender" = $1) as "female_count",
  count("id") filter(where "gender" = $2) as "male_count",
  count("id") filter(where "gender" = $3) as "other_count"
from "person"

filterWhereRef

Adds a filter clause with a nested where clause after the function, where both sides of the operator are references to columns.

filterWhereRef<
  LRE extends ReferenceExpression<DB, TB>,
  RRE extends ReferenceExpression<DB, TB>,
>(
  lhs: LRE,
  op: ComparisonOperatorExpression,
  rhs: RRE,
): AggregateFunctionBuilder<DB, TB, O>

lhs · ReferenceExpression · required

Left-hand side column reference

op · ComparisonOperatorExpression · required

Comparison operator

rhs · ReferenceExpression · required

Right-hand side column reference

Example:

Count people with same first and last names versus general public:

const result = await db
  .selectFrom('person')
  .select((eb) => [
    eb.fn
      .count<number>('id')
      .filterWhereRef('first_name', '=', 'last_name')
      .as('repeat_name_count'),
    eb.fn.count<number>('id').as('total_count'),
  ])
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

select
  count("id") filter(where "first_name" = "last_name") as "repeat_name_count",
  count("id") as "total_count"
from "person"

over

Adds an over clause (window functions) after the function.

over(
  over?: OverBuilderCallback<DB, TB>,
): AggregateFunctionBuilder<DB, TB, O>

over · OverBuilderCallback

Optional callback that returns an OverBuilder with partition by and order by clauses

Example:

Simple window function:

const result = await db
  .selectFrom('person')
  .select(
    (eb) => eb.fn.avg<number>('age').over().as('average_age')
  )
  .execute()

The generated SQL (PostgreSQL):

select avg("age") over() as "average_age"
from "person"

With partition and order by:

const result = await db
  .selectFrom('person')
  .select(
    (eb) => eb.fn.avg<number>('age').over(
      ob => ob.partitionBy('last_name').orderBy('first_name', 'asc')
    ).as('average_age')
  )
  .execute()

The generated SQL (PostgreSQL):

select avg("age") over(partition by "last_name" order by "first_name" asc) as "average_age"
from "person"

$call

Simply calls the provided function passing this as the only argument. $call returns what the provided function returns.

$call<T>(func: (qb: this) => T): T

func · function · required

Function to call with this builder as argument

$castTo

Casts the expression to the given type.

$castTo<C>(): AggregateFunctionBuilder<DB, TB, C>

C · type parameter · required

The new output type

This method call doesn’t change the SQL in any way. This methods simply returns a copy of this AggregateFunctionBuilder with a new output type.

$notNull

Omit null from the expression’s type.

$notNull(): AggregateFunctionBuilder<DB, TB, Exclude<O, null>>

This function can be useful in cases where you know an expression can’t be null, but Kysely is unable to infer it.

This method call doesn’t change the SQL in any way. This methods simply returns a copy of this with a new output type.

toOperationNode

Converts the builder to an operation node for internal use.

toOperationNode(): AggregateFunctionNode

Related Types

AliasedAggregateFunctionBuilder

AggregateFunctionBuilder with an alias. The result of calling AggregateFunctionBuilder.as.

class AliasedAggregateFunctionBuilder<
  DB,
  TB extends keyof DB,
  O = unknown,
  A extends string = never,
>

DB · object · required

The database schema type

TB · keyof DB · required

The table references

O · any

The output type

A · string

The alias name

OverBuilderCallback

Callback type for the over method.

type OverBuilderCallback<DB, TB extends keyof DB> = (
  builder: OverBuilder<DB, TB>,
) => OverBuilder<any, any>