Counts and aggregate reports

Generate Opportunity before trying these examples. Kysoql uses the org's Describe metadata to decide which fields can be grouped or aggregated.

Count records

Use bare count() when you only need a number:

import { db } from "./db";

const count = await db
  .selectFrom("Opportunity")
  .where("IsClosed", "=", false)
  .select(({ fn }) => fn.count())
  .execute();

Use count(field) or another aggregate when you want rows that can also be grouped, filtered, or sorted:

import { db } from "./db";

const rows = await db
  .selectFrom("Opportunity")
  .select(({ fn }) => fn.count("Id").as("opportunityCount"))
  .execute();

Give row-producing aggregates an alias rather than relying on Salesforce names such as expr0.

Aggregate functions

import { db } from "./db";

const totals = db
  .selectFrom("Opportunity")
  .select(({ fn }) => [
    fn.count("Id").as("opportunityCount"),
    fn.countDistinct("AccountId").as("accountCount"),
    fn.sum("Amount").as("totalAmount"),
    fn.avg("Amount").as("averageAmount"),
    fn.min("CloseDate").as("firstCloseDate"),
    fn.max("CloseDate").as("lastCloseDate"),
  ])
  .where("IsClosed", "=", false);

Function	Result
`count(field)`, `countDistinct(field)`	`number`
`sum(field)`, `avg(field)`	`number \| null`
`min(field)`, `max(field)`	The field value, including `null` when applicable
`format(aggregate)`	A localized string with the aggregate's nullability

Group results

Group before selecting ordinary fields:

import { db } from "./db";

const byStage = db
  .selectFrom("Opportunity")
  .select(({ fn }) => fn.sum("Amount").as("totalAmount"))
  .groupBy("StageName")
  .select("StageName")
  .orderBy("StageName");

You can also use grouping to return distinct grouped values without adding a dummy aggregate:

import { db } from "./db";

const stages = db
  .selectFrom("Opportunity")
  .groupBy("StageName")
  .select("StageName")
  .orderBy("StageName");

This is SOQL grouping, not a general SQL distinct() method.

Filter groups with HAVING

WHERE filters source records. HAVING filters the grouped result.

import { db } from "./db";

const significantStages = db
  .selectFrom("Opportunity")
  .select(({ fn }) => [
    fn.count("Id").as("opportunityCount"),
    fn.sum("Amount").as("totalAmount"),
  ])
  .where("IsClosed", "=", false)
  .groupBy("StageName")
  .select("StageName")
  .having((eb) =>
    eb.and([
      eb(eb.fn.count("Id"), ">", 5),
      eb(eb.fn.sum("Amount"), ">=", 100_000),
    ]),
  );

Order by an aggregate

Use the aggregate expression itself rather than its alias:

import { db } from "./db";

const ranked = db
  .selectFrom("Opportunity")
  .select(({ fn }) => fn.sum("Amount").as("totalAmount"))
  .groupBy("StageName")
  .select("StageName")
  .orderBy(({ fn }) => fn.sum("Amount"), "desc", "last")
  .orderBy("StageName");

Rollups and cubes

import { db } from "./db";

const pipeline = db
  .selectFrom("Opportunity")
  .select(({ fn }) => fn.sum("Amount").as("totalAmount"))
  .groupByRollup(["StageName", "Type"])
  .select(["StageName", "Type"])
  .select(({ fn }) => [
    fn.grouping("StageName").as("stageSubtotal"),
    fn.grouping("Type").as("typeSubtotal"),
  ]);

Use groupByCube() for cube grouping. Rollup and cube accept up to three fields. Their subtotal rows can make grouping fields nullable. fn.grouping(field) returns 0 | 1, where 1 marks a subtotal for that field.

For calendar-style reports, continue with date functions. For Apex output, see Compile for Apex.

Counts and aggregate reports

Count recordsCopy Anchor Link

Aggregate functionsCopy Anchor Link

Group resultsCopy Anchor Link

Filter groups with HAVINGCopy Anchor Link

Order by an aggregateCopy Anchor Link

Rollups and cubesCopy Anchor Link

Aggregate query rules worth knowing