How to aggregate data with Knex — Intermediate

SQL / Knex Knex queries

Count, group, or summarize rows using query builder aggregates and JavaScript post-processing.

Learn: Aggregates answer summary questions: how many rows, totals, averages. Use count(), sum(), or raw selects with GROUP BY; post-process in JavaScript when shaping nested API responses.

Supplementary examples

Count pastes per user

return knex("pastes")
  .select("user_id")
  .count("* as paste_count")
  .groupBy("user_id");

Course example

function listOutOfStockCount() {
return knex("products")
.select("product_quantity_in_stock as out_of_stock")
.count("product_id")
.where({ product_quantity_in_stock: 0 })
.groupBy("out_of_stock");
}

Additional references & examples

Knex.js documentation
Query builder, migrations, seeds, and connection configuration.
PostgreSQL aggregate functions
count, sum, avg, and GROUP BY in SQL.