How does group by works in sequelize?

Question

I am looking for group by queries through Sequelize and cannot seem to find any documentation.

    SELECT column, count(column)  
    FROM table 
    GROUP BY column

Solution 1

issue: https://github.com/sequelize/sequelize/issues/348

User.findAll({
 group: ['field']
})

i use [email protected]

Solution 2

I think you looking for something like this:

 Table.findAll({
   attributes: ['column1', 
     sequelize.fn('count', sequelize.col('column2'))], 
   group: ["Table.column1"]
 }).success(function (result) { });

Update: Newer versions of Sequelize uses .then instead of .success.

 Table.findAll({
   attributes: ['column1', 
     sequelize.fn('count', sequelize.col('column2'))], 
   group: ["Table.column1"]
 }).then(function (result) { });

Solution 3

Group by with count sequelize ORM

'field' - custom user input, you can rename this string, it's your field(column) in database

'count' - reserved sequelize keyword string need for get count in sequelize

'cnt' - custom user input, you can rename this string, it's your output count

Sequelize version 3.25.0

User.findAll({
      attributes: ['field', [sequelize.fn('count', sequelize.col('field')), 'cnt']],
      group: ['field'],
})

Solution 4

Try this -

Table.count(
{
   attributes: ['column'], 
   group: 'column',
} 

Solution 5

Example: how to add an convenient alias to the grouped function column.

I did this as a separate response mostly because it wouldn't format well in a comment... otherwise I would have just added it to Sampat's answer.

function getSumsBySomeId() {
  const criteria = {
    attributes: ['some_id', [sequelize.fn('sum', sequelize.col('some_count')), 'some_count_sum']],
    group: ['some_id'],
    raw: true
  };
  return Table.getAll(criteria);
}

YIELDS:

{ some_id: 42, some_count_sum: 100 },
{ some_id: 43, some_count_sum: 150 }
...
etc.

View more solutions

Comments

[Domi]

Wow, this is great. How did you know that?

[lee penkman]

Newer versions of sequelize now use .then(function (result) { }); instead of .success(function (result) { });

[Ante]

is there any documentation on this?

[Maria Ines Parnisari]

@Ante docs.sequelizejs.com/en/latest/docs/models-usage/…

[Ante]

@mparnisari sorry, I missed this part "Everything you see below can also be done for group"

[Maria Ines Parnisari]

No worries. @user964287 this should be marked as the correct answer!

[catamphetamine]

Meaningless SQL query

[Lucio Mollinedo]

Awesome answer is awesome. I've been struggling a bit with sequelize's documentation. This was of much help.