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.
