Interface FunctionModule<DB, TB>

Creates an aggregate function call.

This is a specialized version of the fn method, that returns an AggregateFunctionBuilder instance. A builder that allows you to chain additional methods such as distinct, filterWhere and over.

See avg, count, countAll, max, min, sum shortcuts of common aggregate functions.

Examples

db.selectFrom('person')
  .select(({ fn }) => [
    fn.agg<number>('rank').over().as('rank'),
    fn.agg<string>('group_concat', ['first_name']).distinct().as('first_names')
  ])
Copy

The generated SQL (MySQL):

select rank() over() as "rank",
  group_concat(distinct "first_name") as "first_names"
from "person"
Copy

Calls the any function for the column or expression given as the argument.

The argument must be a subquery or evaluate to an array.

Examples

In the following example, nicknames is assumed to be a column of type string[]:

db.selectFrom('person')
  .selectAll('person')
  .where((eb) => eb(
    eb.val('Jen'), '=', eb.fn.any('person.nicknames')
  ))
Copy

The generated SQL (PostgreSQL):

select
  "person".*
from
  "person"
where
 $1 = any("person"."nicknames")
Copy

Calls the avg function for the column or expression given as the argument.

This sql function calculates the average value for a given column.

For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.

Examples

db.selectFrom('toy')
  .select((eb) => eb.fn.avg('price').as('avg_price'))
  .execute()
Copy

The generated SQL (PostgreSQL):

select avg("price") as "avg_price" from "toy"
Copy

You can limit column range to only columns participating in current query:

db.selectFrom('toy')
  .select((eb) => eb.fn.avg('price').as('avg_price'))
  .execute()
Copy

If this function is used in a select statement, the type of the selected expression will be number | string by default. This is because Kysely can't know the type the db driver outputs. Sometimes the output can be larger than the largest javascript number and a string is returned instead. Most drivers allow you to configure the output type of large numbers and Kysely can't know if you've done so.

You can specify the output type of the expression by providing the type as the first type argument:

db.selectFrom('toy')
  .select((eb) => eb.fn.avg<number>('price').as('avg_price'))
  .execute()
Copy

Sometimes a null is returned, e.g. when row count is 0, and no group by was used. It is highly recommended to include null in the output type union and handle null values in post-execute code, or wrap the function with a coalesce function.

db.selectFrom('toy')
  .select((eb) => eb.fn.avg<number | null>('price').as('avg_price'))
  .execute()
Copy

Calls the coalesce function for given arguments.

This sql function returns the first non-null value from left to right, commonly used to provide a default scalar for nullable columns or functions.

If this function is used in a select statement, the type of the selected expression is inferred in the same manner that the sql function computes. A union of arguments' types - if a non-nullable argument exists, it stops there (ignoring any further arguments' types) and exludes null from the final union type.

(string | null, number | null) is inferred as string | number | null.

(string | null, number, Date | null) is inferred as string | number.

(number, string | null) is inferred as number.

Examples

db.selectFrom('participant')
  .select((eb) => eb.fn.coalesce('nickname', sql<string>`'<anonymous>'`).as('nickname'))
  .where('room_id', '=', roomId)
  .execute()
Copy

The generated SQL (PostgreSQL):

select coalesce("nickname", '<anonymous>') as "nickname"
from "participant" where "room_id" = $1
Copy

You can limit column range to only columns participating in current query:

db.selectFrom('participant')
  .select((eb) =>
    eb.fn.coalesce('nickname', sql<string>`'<anonymous>'`).as('nickname')
  )
  .where('room_id', '=', roomId)
  .execute()
Copy

You can combine this function with other helpers in this module:

db.selectFrom('person')
  .select((eb) => eb.fn.coalesce(eb.fn.avg<number | null>('age'), sql<number>`0`).as('avg_age'))
  .where('first_name', '=', 'Jennifer')
  .execute()
Copy

The generated SQL (PostgreSQL):

select coalesce(avg("age"), 0) as "avg_age" from "person" where "first_name" = $1
Copy

Calls the count function for the column or expression given as the argument.

When called with a column as argument, this sql function counts the number of rows where there is a non-null value in that column.

For counting all rows nulls included (count(*)), see countAll.

For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.

Examples

db.selectFrom('toy')
  .select((eb) => eb.fn.count('id').as('num_toys'))
  .execute()
Copy

The generated SQL (PostgreSQL):

select count("id") as "num_toys" from "toy"
Copy

If this function is used in a select statement, the type of the selected expression will be number | string | bigint by default. This is because Kysely can't know the type the db driver outputs. Sometimes the output can be larger than the largest javascript number and a string is returned instead. Most drivers allow you to configure the output type of large numbers and Kysely can't know if you've done so.

You can specify the output type of the expression by providing the type as the first type argument:

db.selectFrom('toy')
  .select((eb) => eb.fn.count<number>('id').as('num_toys'))
  .execute()
Copy

You can limit column range to only columns participating in current query:

db.selectFrom('toy')
  .select((eb) => eb.fn.count('id').as('num_toys'))
  .execute()
Copy

Calls the count function with * or table.* as argument.

When called with * as argument, this sql function counts the number of rows, nulls included.

For counting rows with non-null values in a given column (count(column)), see count.

For additional functionality such as filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.

Examples

db.selectFrom('toy')
  .select((eb) => eb.fn.countAll().as('num_toys'))
  .execute()
Copy

The generated SQL (PostgreSQL):

select count(*) as "num_toys" from "toy"
Copy

If this is used in a select statement, the type of the selected expression will be number | string | bigint by default. This is because Kysely can't know the type the db driver outputs. Sometimes the output can be larger than the largest javascript number and a string is returned instead. Most drivers allow you to configure the output type of large numbers and Kysely can't know if you've done so.

You can specify the output type of the expression by providing the type as the first type argument:

db.selectFrom('toy')
  .select((eb) => eb.fn.countAll<number>().as('num_toys'))
  .execute()
Copy

Some databases, such as PostgreSQL, support scoping the function to a specific table:

db.selectFrom('toy')
  .innerJoin('pet', 'pet.id', 'toy.pet_id')
  .select((eb) => eb.fn.countAll('toy').as('num_toys'))
  .execute()
Copy

The generated SQL (PostgreSQL):

select count("toy".*) as "num_toys"
from "toy" inner join "pet" on "pet"."id" = "toy"."pet_id"
Copy

You can limit table range to only tables participating in current query:

db.selectFrom('toy')
  .innerJoin('pet', 'pet.id', 'toy.pet_id')
  .select((eb) => eb.fn.countAll('toy').as('num_toys'))
  .execute()
Copy

Calls the max function for the column or expression given as the argument.

This sql function calculates the maximum value for a given column.

For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.

If this function is used in a select statement, the type of the selected expression will be the referenced column's type. This is because the result is within the column's value range.

Examples

db.selectFrom('toy')
  .select((eb) => eb.fn.max('price').as('max_price'))
  .execute()
Copy

The generated SQL (PostgreSQL):

select max("price") as "max_price" from "toy"
Copy

You can limit column range to only columns participating in current query:

db.selectFrom('toy')
  .select((eb) => eb.fn.max('price').as('max_price'))
  .execute()
Copy

Sometimes a null is returned, e.g. when row count is 0, and no group by was used. It is highly recommended to include null in the output type union and handle null values in post-execute code, or wrap the function with a coalesce function.

db.selectFrom('toy')
  .select((eb) => eb.fn.max<number | null, 'price'>('price').as('max_price'))
  .execute()
Copy

Calls the min function for the column or expression given as the argument.

This sql function calculates the minimum value for a given column.

For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.

If this function is used in a select statement, the type of the selected expression will be the referenced column's type. This is because the result is within the column's value range.

Examples

db.selectFrom('toy')
  .select((eb) => eb.fn.min('price').as('min_price'))
  .execute()
Copy

The generated SQL (PostgreSQL):

select min("price") as "min_price" from "toy"
Copy

You can limit column range to only columns participating in current query:

db.selectFrom('toy')
  .select((eb) => eb.fn.min('price').as('min_price'))
  .execute()
Copy

Sometimes a null is returned, e.g. when row count is 0, and no group by was used. It is highly recommended to include null in the output type union and handle null values in post-execute code, or wrap the function with a coalesce function.

db.selectFrom('toy')
  .select((eb) => eb.fn.min<number | null, 'price'>('price').as('min_price'))
  .execute()
Copy

Calls the sum function for the column or expression given as the argument.

This sql function sums the values of a given column.

For additional functionality such as distinct, filtering and window functions, refer to AggregateFunctionBuilder. An instance of this builder is returned when calling this function.

Examples

db.selectFrom('toy')
  .select((eb) => eb.fn.sum('price').as('total_price'))
  .execute()
Copy

The generated SQL (PostgreSQL):

select sum("price") as "total_price" from "toy"
Copy

You can limit column range to only columns participating in current query:

db.selectFrom('toy')
  .select((eb) => eb.fn.sum('price').as('total_price'))
  .execute()
Copy

If this function is used in a select statement, the type of the selected expression will be number | string by default. This is because Kysely can't know the type the db driver outputs. Sometimes the output can be larger than the largest javascript number and a string is returned instead. Most drivers allow you to configure the output type of large numbers and Kysely can't know if you've done so.

You can specify the output type of the expression by providing the type as the first type argument:

db.selectFrom('toy')
  .select((eb) => eb.fn.sum<number>('price').as('total_price'))
  .execute()
Copy

Sometimes a null is returned, e.g. when row count is 0, and no group by was used. It is highly recommended to include null in the output type union and handle null values in post-execute code, or wrap the function with a coalesce function.

db.selectFrom('toy')
  .select((eb) => eb.fn.sum<number | null>('price').as('total_price'))
  .execute()
Copy