Aggregate Queries

Use querier.aggregate() for analytics that involve GROUP BY, aggregate functions, and post-aggregation filtering via HAVING. Works identically across all SQL dialects and MongoDB.

Basic Usage

You write

import { pool } from './uql.config.js';
import { Order } from './shared/models/index.js';

const results = await pool.aggregate(Order, {
  $where: { amount: { $gt: 0 } }, // WHERE: filter rows before grouping
  $group: { status: true }, // GROUP BY column(s)
  $agg: {
    total: { $sum: 'amount' }, // SUM("amount") AS "total"
    count: { $count: '*' }, // COUNT(*) AS "count"
  },
  $having: { count: { $gt: 5 } }, // Post-aggregation filter
  $sort: { total: -1 }, // ORDER BY total DESC
  $limit: 10,
});

PostgreSQL

SELECT "status", SUM("amount") "total", COUNT(*) "count"
FROM "Order"
WHERE "amount" > $1
GROUP BY "status"
HAVING COUNT(*) > $2
ORDER BY SUM("amount") DESC
LIMIT 10

MySQL / MariaDB / SQLite

SELECT `status`, SUM(`amount`) `total`, COUNT(*) `count`
FROM `Order`
WHERE `amount` > ?
GROUP BY `status`
HAVING COUNT(*) > ?
ORDER BY SUM(`amount`) DESC
LIMIT 10

MongoDB

[
  { "$match": { "amount": { "$gt": 0 } } },
  {
    "$group": {
      "_id": { "status": "$status" },
      "total": { "$sum": "$amount" },
      "count": { "$sum": 1 }
    }
  },
  { "$project": { "_id": 0, "status": "$_id.status", "total": 1, "count": 1 } },
  { "$match": { "count": { "$gt": 5 } } },
  { "$sort": { "total": -1 } },
  { "$limit": 10 }
]

There is no $select in aggregate(): the output columns are exactly the $group columns plus the $agg aliases.

$group and $agg

$group lists the columns to group by; $agg defines the computed columns, each under an alias you choose. Keeping them separate makes both fully type-safe: $group keys are checked against your entity’s fields (like $select), and the field references inside $agg are typed too, so a typo is a compile error. An alias may not repeat a $group column, since both would come back under that one name.

$sum and $avg accept numeric columns only, their result being a number. Every op except $count is typed | null: SQL aggregates NULL over an empty group, and an ungrouped aggregate still returns one row, so a $where matching nothing hands you a row of nulls. $count answers 0 there instead.

The $agg ops, each under an alias of your choosing:

• { $count: '*' }: COUNT(*), every row
• { $count: 'field' }: COUNT("field"), non-null values only
• { $sum: 'field' }: SUM("field")
• { $avg: 'field' }: AVG("field")
• { $min: 'field' }: MIN("field")
• { $max: 'field' }: MAX("field")
• { $countDistinct: 'field' }: COUNT(DISTINCT "field")
• { $sumDistinct: 'field' }: SUM(DISTINCT "field")
• { $avgDistinct: 'field' }: AVG(DISTINCT "field")

Both keys are optional: $group alone is a DISTINCT-style query, $agg alone a grand total across all rows:

You write

const [{ revenue }] = await pool.aggregate(Order, {
  $agg: { revenue: { $sum: 'amount' } },
});

PostgreSQL

SELECT SUM("amount") "revenue" FROM "Order"

MySQL / MariaDB / SQLite

SELECT SUM(`amount`) `revenue` FROM `Order`

Distinct aggregates

$countDistinct, $sumDistinct and $avgDistinct aggregate over a field’s distinct values, e.g. how many distinct customers ordered per status:

You write

const results = await pool.aggregate(Order, {
  $group: { status: true },
  $agg: { customers: { $countDistinct: 'customerId' } },
});

PostgreSQL

SELECT "status", COUNT(DISTINCT "customerId") "customers"
FROM "Order"
GROUP BY "status"

On MongoDB this compiles to $addToSet + a $project reducer ($size for $countDistinct, $sum/$avg for $sumDistinct/$avgDistinct), so the result is identical across dialects. DISTINCT applies only to these numeric aggregates; $min/$max have no distinct variant.

$where vs $having

• $where: Filters rows before grouping (WHERE clause).
• $having: Filters groups after aggregation (HAVING clause).

You write

const results = await pool.aggregate(Order, {
  $where: { createdAt: { $gte: new Date('2025-01-01') } },
  $group: { status: true },
  $agg: { count: { $count: '*' } },
  $having: { count: { $gt: 10 } },
});

PostgreSQL

SELECT "status", COUNT(*) "count"
FROM "Order"
WHERE "createdAt" >= $1
GROUP BY "status"
HAVING COUNT(*) > $2

MySQL / MariaDB / SQLite

SELECT `status`, COUNT(*) `count`
FROM `Order`
WHERE `createdAt` >= ?
GROUP BY `status`
HAVING COUNT(*) > ?

$having Operators

The $having map supports the same comparison operators as $where:

Operator	SQL	Example
$eq	=	{ count: 5 } or { count: { $eq: 5 } }
$ne	<>	{ count: { $ne: 0 } }
$gt / $gte	> / >=	{ total: { $gte: 100 } }
$lt / $lte	< / <=	{ avg: { $lt: 50 } }
$between	BETWEEN	{ count: { $between: [5, 20] } }
$in / $nin	IN / NOT IN	{ count: { $in: [1, 5, 10] } }
$isNull	IS NULL	{ maxVal: { $isNull: true } }
$isNotNull	IS NOT NULL	{ maxVal: { $isNotNull: true } }

Sorting, Pagination

Results sort by any alias and page with $skip / $limit:

You write

import { User } from './shared/models/index.js';

const results = await pool.aggregate(User, {
  $group: { status: true },
  $agg: { count: { $count: '*' } },
  $sort: { count: -1 },
  $skip: 20,
  $limit: 10,
});

$distinct

For simple SELECT DISTINCT queries (without aggregation), add $distinct: true to any find query:

You write

const names = await pool.findMany(User, {
  $select: { name: true },
  $distinct: true,
});

PostgreSQL

SELECT DISTINCT "name" FROM "User"

MySQL / MariaDB / SQLite

SELECT DISTINCT `name` FROM `User`

$distinct is a modifier on findMany, not part of aggregate(). Use aggregate() when you need GROUP BY, aggregate functions, or HAVING filters.

---

Next Steps

• Querier API: Full find/select/sort/pagination reference.
• Logical Operators: Compose the $where that runs before grouping.
• Comparison Operators: Every operator usable in $having.
• Sub-Queries: Correlated sub-queries and raw SQL fragments.