AggregationsSupported

Grouped aggregates — count, sum, avg, min, max with GROUP BY and HAVING — compiled to real SQL and verified against PostgreSQL in the benchmarks.

Count#

import { postgres } from '@zmdb/postgres';
import { createQueryCompiler, trustedTable } from '@zmdb/sql';

createQueryCompiler(postgres).selectFrom(trustedTable('orders')).count('id', 'orderCount').compile();

SELECT COUNT("id") AS "orderCount" FROM "orders"

Group by + multiple aggregates#

import { postgres } from '@zmdb/postgres';
import { createQueryCompiler, trustedTable } from '@zmdb/sql';

createQueryCompiler(postgres).selectFrom(trustedTable('orders')).select(['userId']).count('id', 'orderCount').sum('total', 'revenue').groupBy('userId').compile();

SELECT "userId", COUNT("id") AS "orderCount", SUM("total") AS "revenue"
FROM "orders" GROUP BY "userId"

Having#

Filter on an aggregate with having:

import { postgres } from '@zmdb/postgres';
import { createQueryCompiler, trustedTable } from '@zmdb/sql';

createQueryCompiler(postgres).selectFrom(trustedTable('orders')).select(['userId']).count('id', 'orderCount').groupBy('userId').having('orderCount', '>', 5).compile();

SELECT "userId", COUNT("id") AS "orderCount" FROM "orders"
GROUP BY "userId" HAVING COUNT("id") > $1