Group & Aggregate Records in Sequelize

In SQL, grouping and aggregation are used to summarize and analyze data.

Grouping allows us to group records that share a common column, while aggregation functions such as COUNT, SUM, AVG, MAX, and MIN allow us to perform calculations on groups of data.

Aggregation functions

To execute an aggregation function like COUNT or SUM on an column, you can use the Sequelize.fn() static method:

{
  attributes: [
    [Sequelize.fn(<function>, Sequelize.col(<attribute>)), <alias>?]
  ]
}

Where:

• function is a string containing the name of the aggregation function.
• attribute is a string containing the name of the column to perform an aggregation on.
• alias is an optional string containing the alias of the column.

Note: The Sequelize.col() static method is used to ensure that column names are properly interpreted as columns and not strings.

Example

For example, this query will use the COUNT and SUM functions to aggregate under the total_employees and total_salaries columns the total number of records based on the id column and the sum of the values of records of the salary attribute:

await Employee.findAll({
  attributes: [
    [Sequelize.fn('COUNT', Sequelize.col('id')), 'total_employees'],
    [Sequelize.fn('SUM', Sequelize.col('salary')), 'total_salaries']
  ]
});

Which is equivalent to this SQL statement:

mysql> SELECT COUNT(id) AS total_employees, SUM(salary) AS total_salaries FROM employees;
+-----------------+----------------+
| total_employees | total_salaries |
+-----------------+----------------+
|              10 |         594000 |
+-----------------+----------------+
1 row in set (0.00 sec)

Group rows with identical values

To improve summarized result sets, you can group rows together based on identical column values using the group property:

{
  attributes: [<attributes>],
  group: <attribute>
}

Example

For example, this query will retrieve the department column and aggregate the salary column using the AVG function, then group the results by department:

await Employee.findAll({
  attributes: [
    'department',
    [Sequelize.fn('AVG', Sequelize.col('salary')), 'avg_salary']
  ],
  group: 'department'
});

Which is equivalent to this SQL statement:

mysql> SELECT department, AVG(salary) FROM employees GROUP BY department;
+-------------+-------------+
| department  | AVG(salary) |
+-------------+-------------+
| Sales       |  52333.3333 |
| Engineering |  74000.0000 |
| HR          |  46500.0000 |
| Marketing   |  61000.0000 |
+-------------+-------------+
4 rows in set (0.01 sec)

Filter aggregated records

To filter aggregated records based on a condition, you can combine the group property with the having property:

{
  attributes: [<attribute>, ...],
  group: <attribute>,
  having: {
    <attribute>: {
      [Op.<operator>]: <value>
    }
  }
}

Example

For example, this query will retrieve the department column and aggregate the salary column using the AVG function under a column named average_salary, group the results by the department column, and filter the results where the value of the average_salary column is greater than 60000:

await Employee.findAll({
  attributes: [
    'department',
    [Sequelize.fn('AVG', Sequelize.col('salary')), 'average_salary']
  ],
  group: 'department',
  having: {
    average_salary: {
      [Op.gt]: 60000
    }
  }
});

Which is equivalent to this SQL statement:

mysql> SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department HAVING average_salary > 60000;
+-------------+----------------+
| department  | average_salary |
+-------------+----------------+
| Engineering |     74000.0000 |
| Marketing   |     61000.0000 |
+-------------+----------------+
2 rows in set (0.00 sec)

Summary

Here's a summary of what you've learned in this lesson:

• The Sequelize.fn() static method is used to execute an aggregate function.
• The Sequelize.col() static method is used to reference a column when using an aggregate function.
• The group property is the equivalent of the GROUP BY SQL clause.
• The having property is the equivalent of the HAVING SQL clause.