How does group by work in sequelize?

Understanding Group By

With Sequelize, GROUP BY operations are made quite simple. The group option is a part of the findAll method where you specify the fields that you want to group by.

Stay updated, friends

Consider keeping Sequelize updated so that you have the latest features and improvements that can actually affect Group By operations.

Fun with fn and col

Sequelize's sequelize.fn is your ticket to SQL aggregate functions, and sequelize.col helps you pick the columns for these aggregations.

const totalPrice = await Product.findAll({
  attributes: [
    'category',
    [sequelize.fn('SUM', sequelize.col('price')), 'totalPrice'] // Robots love SUM(finance)!
  ],
  group: ['category']
});


You’ve got raw data, use aliases

When you need just the plain data, use raw: true. Also, assign aliases for more readability:

const groupData = await Model.findAll({
  attributes: [
    [sequelize.fn('COUNT', sequelize.col('status')), 'StatusCount'] // Get the headcount!
  ],
  group: ['status'],
  raw: true
});


Oops, handle with care

Avoid using SQL reserved words for column names and aliases. Always check your Sequelize version when looking up documentation about GROUP BY.

Complex scenarios mastered

Let's lever the basics to solve more complex scenarios involving group by functionality.

Counting associations like a pro

For dealing with grouped data across associations, your job is to make sure the tables are joined appropriately and the correct field is chosen for grouping.

const orderCount = await Customer.findAll({
  attributes: [
    [sequelize.fn('COUNT', sequelize.col('orders.id')), 'orderCount']
  ],
  include: [{
    model: Order, // They have an order to be here!
    attributes: []
  }],
  group: ['Customer.id']
});


Dark arts of handling reserved keywords

Use the literal function to handle naming collisions with reserved keywords.

const dataWithReservedKeywords = await Model.findAll({
  attributes: [
    'id',
    [sequelize.fn('COUNT', sequelize.col('"order"."id"')), 'orderCount']
  ],
  group: [sequelize.literal('1')] // That’s lit!
});


Time traveling with dates

You can group by dates to analyze your data over specific time intervals. Like this:

const dailySales = await Sale.findAll({
  attributes: [
    [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'],
    [sequelize.fn('SUM', sequelize.col('amount')), 'totalSales']
  ],
  group: [sequelize.fn('DATE', sequelize.col('createdAt'))] // Happy birthday to the deal!
});


Be the group master

Here are some advanced tips to help you tame the Group By beast.

Indexing: a love story

For optimized query performance, ensure that columns used in the GROUP BY clause are indexed. A must-have for a smooth date with large datasets.

A match made in heaven: GROUP BY with having

Filter aggregated data using the having function. It is SQL's way of applying conditions after aggregations.

const havingExample = await Model.findAll({
  attributes: [
    'groupColumn',
    [sequelize.fn('COUNT', sequelize.col('dataColumn')), 'dataCount'] // Count Dracula approves!
  ],
  group: 'groupColumn',
  having: sequelize.where(sequelize.fn('COUNT', sequelize.col('dataColumn')), '>', 5)
});


The single life is overrated

Grouping based on multiple columns is a reality and Sequelize allows you to pass them as an array.

const compositeGrouping = await Model.findAll({
  group: ['columnA', 'columnB'] // Power couple!
});


Remember to align with your database validation rules so as not to break any constraints.