Aggregation Operations in TypeORM (Sum, Avg, Min, Max, Count)

This short and straightforward article shows you how to perform aggregation operations in TypeORM. We’ll use a query builder and aggregate functions to calculate the following:

• Sum: The sum of the values
• Avg: The average value
• Min: The minimum value
• Max: The biggest value
• Count: Count the records

Let’s say we have a table called employee that stores information about names, salaries of staff in a fiction company. Here’s the entity Employee defined with TypeORM:

// KindaCode.com Example
// Employee Entity
import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Employee {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  salary: number;
}

You can add some dummy data to the table like so:

Now we can use a query builder to find the total salary, average salary, highest salary, smallest salary, and count the number of employees as follows:

const employeeRepository = myDataSource.getRepository(Employee);
const result = await employeeRepository
      .createQueryBuilder('employee')
      .select('SUM(employee.salary)', 'totalSalary')
      .addSelect('AVG(employee.salary)', 'averageSalary')
      .addSelect('MAX(employee.salary)', 'maxSalary')
      .addSelect('MIN(employee.salary)', 'minSalary')
      .addSelect('COUNT(*)', 'count')
      .getRawOne(); 

console.log(result);

Output:

{
  totalSalary: '45000',
  averageSalary: '7500.0000000000000000',
  maxSalary: 12000,
  minSalary: 3000,
  count: '6'
}

That’s it, my friend. Continue learning more new and interesting stuff about TypeORM by taking a look at the following articles:

• TypeORM: Select the Most Recent Record (2 Examples)
• TypeORM: Using LIKE Operator (2 Examples)
• TypeORM Upsert: Update If Exists, Create If Not Exists
• How to Store JSON Object with TypeORM
• TypeORM: Adding created_at and updated_at columns
• TypeORM: Selecting Rows Between 2 Dates

You can also check out our Javascript category page, TypeScript category page, Node.js category page, and React category page for the latest tutorials and examples.