How to add GROUP BY clause to a Sequelize find method

In Sequelize, you can add the group option in your query method findAll() to add the GROUP BY clause to the generated SQL query.

For example, suppose you have a Users table with the following data:

+----+-----------+----------+
| id | firstName | isActive |
+----+-----------+----------+
|  1 | Nathan    |        1 |
|  2 | John      |        0 |
|  3 | Nathan    |        0 |
|  4 | Jane      |        0 |
|  5 | Jane      |        1 |
+----+-----------+----------+

Now you want to select all firstName values and group any duplicate values of the column.

Here’s the code for calling the findAll() method on the model:

const users = await User.findAll({
  attributes: ["firstName"],
  group: "firstName",
});

The Sequelize group option accepts a string or an array. You can pass a string when you group the result only by one column.

But when you want to group by multiple columns, use an array.

The above findAll() method generates the following SQL query:

SELECT `firstName`
  FROM `Users` AS `User`
  GROUP BY `firstName`;

And that’s how you add the GROUP BY clause when using Sequelize. Let’s look at how to order the returned values next.

Sequelize group by and ordering

The group option of Sequelize only allows you to group the result of your query.

If you want to also sort the result order, you need to use the order option as follows:

const users = await User.findAll({
  attributes: ["firstName"],
  group: "firstName",
  order: ["firstName", "ASC"],
});

The generated SQL query is as shown below:

SELECT `firstName`
  FROM `Users` AS `User`
  GROUP BY `firstName`
  ORDER BY `User`.`firstName` ASC;

For more information, see: Adding ORDER BY clause to Sequelize ORM query

Sequelize group by with aggregate functions

Finally, you can use the group option with aggregate functions as well.

The following query counts the isActive column row grouped by the firstName column:

const users = User.findAll({
  attributes: [
    "firstName",
    [sequelize.fn("COUNT", sequelize.col("isActive")), "count_isActive"],
  ],
  group: "firstName",
});

The generated SQL query from the code above will be as shown below:

SELECT `firstName`,
    COUNT(`isActive`) AS `count_isActive`
  FROM `Users` AS `User`
  GROUP BY `firstName`;

And that’s how you use the Sequelize group option with aggregate functions.