Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 37 ยท Querying

Aggregates and Grouping

Compute count, sum, average, min and max, group rows with GROUP BY and HAVING, and map results in TypeORM and Sequelize, with runnable SQL stand-ins you can edit.

Reports and dashboards rarely need individual rows. They need numbers: how many orders per customer, total revenue per month, the average rating. These are aggregates, computed by the database in one pass. Pulling every row into Node to add them up is slower and uses far more memory.

Library code is read-only; the runnable blocks run the equivalent SQL on node:sqlite.

Simple aggregates

TypeScript
// TypeORM
const total = await orders.count({ where: { status: "paid" } });
const { sum } = await orders
  .createQueryBuilder("o")
  .select("SUM(o.totalCents)", "sum")
  .where("o.status = :s", { s: "paid" })
  .getRawOne();

const agg = await orders.sum("totalCents", { status: "paid" });     // repository helpers
const avg = await orders.average("totalCents");
const max = await orders.maximum("totalCents");
TypeScript
// Sequelize
const total = await Order.count({ where: { status: "paid" } });
const sum = await Order.sum("totalCents", { where: { status: "paid" } });
const avg = await Order.aggregate("totalCents", "avg");
const max = await Order.max("totalCents");
const min = await Order.min("totalCents");

Note that SUM over zero rows returns NULL (and sum() helpers may return null or 0 depending on the library version), so default it: sum ?? 0. DECIMAL sums arrive as strings in many drivers.

GROUP BY

Grouping splits rows into buckets and aggregates each bucket.

TypeScript
// TypeORM
const perCustomer = await orders
  .createQueryBuilder("o")
  .select("o.customerId", "customerId")
  .addSelect("COUNT(*)", "orders")
  .addSelect("SUM(o.totalCents)", "revenue")
  .groupBy("o.customerId")
  .having("SUM(o.totalCents) > :min", { min: 10000 })
  .orderBy("revenue", "DESC")
  .getRawMany();
TypeScript
// Sequelize
import { fn, col } from "sequelize";

const perCustomer = await Order.findAll({
  attributes: [
    "customerId",
    [fn("COUNT", col("id")), "orders"],
    [fn("SUM", col("totalCents")), "revenue"],
  ],
  group: ["customerId"],
  having: literal("SUM(total_cents) > 10000"),
  order: [[literal("revenue"), "DESC"]],
  raw: true,
});

WHERE filters rows before grouping; HAVING filters groups after aggregation. raw: true (Sequelize) and getRawMany() (TypeORM) return plain objects, because grouped rows are no longer entities.

Node.js GROUP BY and HAVING

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Counting rows correctly

COUNT(*) counts rows, COUNT(column) counts non-null values, and COUNT(DISTINCT column) counts unique values. Picking the wrong one is a classic reporting bug.

Node.js COUNT variants

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Grouping by date

Dashboards group by day or month. The function that truncates a date differs by database (date_trunc('month', created_at) in PostgreSQL, DATE_FORMAT in MySQL, strftime in SQLite), so this is a place where raw SQL or fn() with a database-specific function is normal.

Node.js Revenue per month

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Aggregates with relations

Counting related rows (posts per author) tempts you to load authors with all their posts and .length them. Use a grouped query or a relation count instead:

TypeScript
// TypeORM: loadRelationCountAndMap
const authors = await AppDataSource.getRepository(Author)
  .createQueryBuilder("a")
  .loadRelationCountAndMap("a.postCount", "a.posts")
  .getMany();
TypeScript
// Sequelize
const authors = await Author.findAll({
  attributes: { include: [[fn("COUNT", col("posts.id")), "postCount"]] },
  include: [{ model: Post, attributes: [] }],
  group: ["Author.id"],
});

Chapter 4 explains relations; this is the pattern to remember.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between WHERE and HAVING?

    Use WHERE to cut rows first and HAVING for conditions on aggregates.
  2. 2. What does SUM() return over zero rows?

    Aggregates other than COUNT return NULL on empty input, so default the value in code.
  3. 3. Which counts only rows where the column is not NULL?

    COUNT(column) skips NULLs; COUNT(*) counts every row.
  4. 4. Why use raw: true or getRawMany() for grouped results?

    Aggregated rows do not map to a model instance.

Summary

  • Let the database aggregate: count, sum, avg, min, max as helpers or inside builders.
  • GROUP BY buckets rows; WHERE filters before, HAVING after.
  • Grouped results are raw objects; DECIMAL sums may be strings and empty sums are NULL.
  • Date grouping functions differ per database.
  • Count related rows with grouped queries or relation counts, not by loading and measuring arrays.

Next: Many-to-One and One-to-Many Relations