Puri lets you use SQL’s powerful aggregate functions in a type-safe manner. This document explains COUNT, SUM, AVG, MAX, MIN along with GROUP BY and HAVING.
Aggregate Function Overview
COUNT Count records Puri.count()
SUM/AVG Calculate sum and average Puri.sum(), Puri.avg()
MAX/MIN Find max and min values Puri.max(), Puri.min()
GROUP BY Aggregate by group groupBy(), having()
COUNT - Counting Records Basic COUNT Total record count
COUNT specific column
DISTINCT COUNT
const result = await db . table ( "employees" ) . select ({ total: Puri . count (), }) . first (); console . log ( result . total ); // 150 // Type: { total: number }
Puri.count() generates COUNT(*). It counts all rows including NULL.
Puri.count("column") counts only rows where that column is not NULL.
COUNT with GROUP BY const results = await db . table ( "employees" ) . select ({ departmentId: "department_id" , employeeCount: Puri . count ( "id" ), }) . groupBy ( "department_id" ); // Employee count per department // [ // { departmentId: 1, employeeCount: 20 }, // { departmentId: 2, employeeCount: 15 }, // ... // ]
SUM - Sum Calculate the sum of a numeric column.
const result = await db . table ( "employees" ) . select ({ totalSalary: Puri . sum ( "salary" ), }) . first (); console . log ( result . totalSalary ); // 5000000 // Type: { totalSalary: number }
SUM with GROUP BY const results = await db . table ( "employees" ) . select ({ departmentId: "department_id" , totalSalary: Puri . sum ( "salary" ), avgSalary: Puri . avg ( "salary" ), }) . groupBy ( "department_id" ) . orderBy ( "totalSalary" , "desc" ); // Total and average salary per department
AVG - Average Calculate the average of a numeric column.
const result = await db . table ( "employees" ) . select ({ avgSalary: Puri . avg ( "salary" ), }) . first (); console . log ( result . avgSalary ); // 65000 // Type: { avgSalary: number }
MAX / MIN - Maximum / Minimum Maximum const result = await db . table ( "employees" ) . select ({ highestSalary: Puri . max ( "salary" ), latestHireDate: Puri . max ( "hire_date" ), }) . first (); // Highest salary, most recent hire date
Minimum const result = await db . table ( "employees" ) . select ({ lowestSalary: Puri . min ( "salary" ), earliestHireDate: Puri . min ( "hire_date" ), }) . first (); // Lowest salary, oldest hire date
MAX/MIN with GROUP BY const results = await db . table ( "employees" ) . select ({ departmentId: "department_id" , minSalary: Puri . min ( "salary" ), maxSalary: Puri . max ( "salary" ), avgSalary: Puri . avg ( "salary" ), }) . groupBy ( "department_id" ); // Min/max/avg salary per department
GROUP BY - Grouping Group data by specific columns for aggregation.
Single Column GROUP BY const results = await db . table ( "employees" ) . select ({ departmentId: "department_id" , count: Puri . count ( "id" ), avgSalary: Puri . avg ( "salary" ), }) . groupBy ( "department_id" ); // Statistics per department
Multiple Column GROUP BY const results = await db . table ( "employees" ) . select ({ departmentId: "department_id" , hireYear: Puri . rawNumber ( "EXTRACT(YEAR FROM hire_date)" ), count: Puri . count ( "id" ), }) . groupBy ( "department_id" , "hireYear" ) . orderBy ( "department_id" , "asc" ) . orderBy ( "hireYear" , "asc" ); // New employee count by department + year
GROUP BY rules :
All columns in SELECT that are not aggregate functions must be in GROUP BY
Columns not in GROUP BY cannot be SELECTed (except aggregate functions)
GROUP BY with JOIN const results = await db . table ( "departments" ) . leftJoin ( "employees" , "departments.id" , "employees.department_id" ) . select ({ deptId: "departments.id" , deptName: "departments.name" , empCount: Puri . count ( "employees.id" ), avgSalary: Puri . avg ( "employees.salary" ), }) . groupBy ( "departments.id" , "departments.name" ) . orderBy ( "empCount" , "desc" ); // Employee count & average salary per department
HAVING - Filtering Aggregate Results HAVING filters aggregate results after GROUP BY.
Basic HAVING const results = await db . table ( "employees" ) . select ({ departmentId: "department_id" , count: Puri . count ( "id" ), }) . groupBy ( "department_id" ) . having ( "count" , ">=" , 10 ); // Only departments with 10+ employees
WHERE vs HAVING :
WHERE: Filters before GROUP BY (on individual rows)HAVING: Filters after GROUP BY (on aggregate results) HAVING with Aggregate Functions const results = await db . table ( "employees" ) . select ({ departmentId: "department_id" , avgSalary: Puri . avg ( "salary" ), count: Puri . count ( "id" ), }) . groupBy ( "department_id" ) . having ( "avgSalary" , ">" , 70000 ) . having ( "count" , ">=" , 5 ); // Departments with avg salary > 70k AND 5+ employees
WHERE + HAVING Combined const results = await db . table ( "employees" ) . select ({ departmentId: "department_id" , avgSalary: Puri . avg ( "salary" ), }) . where ( "hire_date" , ">=" , "2020-01-01" ) // Row filter . groupBy ( "department_id" ) . having ( "avgSalary" , ">" , 65000 ); // Aggregate filter // Among employees hired since 2020, avg salary > 65k by department
Complex Aggregation Examples Detailed Department Statistics const stats = await db . table ( "departments" ) . leftJoin ( "employees" , "departments.id" , "employees.department_id" ) . select ({ deptId: "departments.id" , deptName: "departments.name" , // Employee count empCount: Puri . count ( "employees.id" ), // Salary statistics minSalary: Puri . min ( "employees.salary" ), maxSalary: Puri . max ( "employees.salary" ), avgSalary: Puri . avg ( "employees.salary" ), totalSalary: Puri . sum ( "employees.salary" ), // Hire date statistics earliestHire: Puri . min ( "employees.hire_date" ), latestHire: Puri . max ( "employees.hire_date" ), }) . groupBy ( "departments.id" , "departments.name" ) . having ( "empCount" , ">" , 0 ) . orderBy ( "empCount" , "desc" );
Project Progress Status const projectStats = await db . table ( "projects" ) . leftJoin ( "projects__employees" , "projects.id" , "projects__employees.project_id" ) . select ({ projectId: "projects.id" , projectName: "projects.name" , status: "projects.status" , memberCount: Puri . count ( "projects__employees.employee_id" ), }) . groupBy ( "projects.id" , "projects.name" , "projects.status" ) . orderBy ( "status" , "asc" ) . orderBy ( "memberCount" , "desc" ); // Member count per project (sorted by status)
Monthly Sales Analysis const monthlySales = await db . table ( "orders" ) . select ({ year: Puri . rawNumber ( "EXTRACT(YEAR FROM created_at)" ), month: Puri . rawNumber ( "EXTRACT(MONTH FROM created_at)" ), orderCount: Puri . count ( "id" ), totalAmount: Puri . sum ( "amount" ), avgAmount: Puri . avg ( "amount" ), }) . where ( "status" , "completed" ) . groupBy ( "year" , "month" ) . orderBy ( "year" , "desc" ) . orderBy ( "month" , "desc" ); // Monthly order statistics
Conditional Aggregation Conditional aggregation using CASE WHEN.
Count by Status const stats = await db . table ( "projects" ) . select ({ totalProjects: Puri . count ( "id" ), activeProjects: Puri . rawNumber ( ` COUNT(CASE WHEN status = 'in_progress' THEN 1 END) ` ), completedProjects: Puri . rawNumber ( ` COUNT(CASE WHEN status = 'completed' THEN 1 END) ` ), cancelledProjects: Puri . rawNumber ( ` COUNT(CASE WHEN status = 'cancelled' THEN 1 END) ` ), }) . first (); // Total/in-progress/completed/cancelled project counts
Range-based Aggregation const salaryRanges = await db . table ( "employees" ) . select ({ under50k: Puri . rawNumber ( ` COUNT(CASE WHEN salary < 50000 THEN 1 END) ` ), range50to70k: Puri . rawNumber ( ` COUNT(CASE WHEN salary BETWEEN 50000 AND 70000 THEN 1 END) ` ), above70k: Puri . rawNumber ( ` COUNT(CASE WHEN salary > 70000 THEN 1 END) ` ), }) . first (); // Employee count by salary range
Practical Examples Dashboard Statistics async getDashboardStats () { // Overall statistics const overall = await this . getPuri ( "r" ) . table ( "employees" ) . select ({ totalEmployees: Puri . count ( "id" ), avgSalary: Puri . avg ( "salary" ), maxSalary: Puri . max ( "salary" ), minSalary: Puri . min ( "salary" ), }) . first (); // Statistics by department const byDepartment = await this . getPuri ( "r" ) . table ( "departments" ) . leftJoin ( "employees" , "departments.id" , "employees.department_id" ) . select ({ deptName: "departments.name" , empCount: Puri . count ( "employees.id" ), avgSalary: Puri . avg ( "employees.salary" ), }) . groupBy ( "departments.id" , "departments.name" ) . orderBy ( "empCount" , "desc" ) . limit ( 10 ); return { overall , byDepartment , }; }
Hire Year Statistics async getHiringTrends () { const trends = await this . getPuri ( "r" ) . table ( "employees" ) . select ({ year: Puri . rawNumber ( "EXTRACT(YEAR FROM hire_date)" ), hireCount: Puri . count ( "id" ), avgSalary: Puri . avg ( "salary" ), }) . groupBy ( "year" ) . orderBy ( "year" , "desc" ) . limit ( 5 ); return trends ; }
Top 5 Departments async getTop5Departments () { const top5 = await this . getPuri ( "r" ) . table ( "departments" ) . leftJoin ( "employees" , "departments.id" , "employees.department_id" ) . select ({ deptId: "departments.id" , deptName: "departments.name" , empCount: Puri . count ( "employees.id" ), totalSalary: Puri . sum ( "employees.salary" ), avgSalary: Puri . avg ( "employees.salary" ), }) . groupBy ( "departments.id" , "departments.name" ) . having ( "empCount" , ">" , 0 ) . orderBy ( "totalSalary" , "desc" ) . limit ( 5 ); return top5 ; }
Index Usage -- Index columns in GROUP BY CREATE INDEX idx_employees_department_id ON employees(department_id); CREATE INDEX idx_employees_hire_date ON employees(hire_date); -- Index columns being aggregated (optional) CREATE INDEX idx_employees_salary ON employees(salary);
// ❌ Bad: Unnecessary aggregations await db . table ( "employees" ) . select ({ count: Puri . count ( "id" ), sum: Puri . sum ( "salary" ), avg: Puri . avg ( "salary" ), max: Puri . max ( "salary" ), min: Puri . min ( "salary" ), // ... more aggregations }) . groupBy ( "department_id" ); // ✅ Good: Only needed ones await db . table ( "employees" ) . select ({ departmentId: "department_id" , count: Puri . count ( "id" ), avgSalary: Puri . avg ( "salary" ), }) . groupBy ( "department_id" );
Filter with WHERE First // ✅ Good: Remove unnecessary rows before aggregation await db . table ( "employees" ) . where ( "is_active" , true ) // Filter first . where ( "hire_date" , ">=" , "2020-01-01" ) . select ({ departmentId: "department_id" , avgSalary: Puri . avg ( "salary" ), }) . groupBy ( "department_id" );
Type Safety Result types of aggregate functions are automatically inferred.
const result = await db . table ( "employees" ) . select ({ count: Puri . count ( "id" ), // number avgSalary: Puri . avg ( "salary" ), // number maxDate: Puri . max ( "hire_date" ), // string (dates return as string) }) . first (); result . count ; // number result . avgSalary ; // number result . maxDate ; // string
Next Steps