Sequelize Aggregate Functions (MIN, MAX, SUM, COUNT, etc.) Examples

Most of you're using ORM for connecting your application to database. For those using SQL databases in Node.js platform, Sequelize is one of the most popular options. Sometimes you may need to use aggregate functions when fetching data from database, such as SUM, COUNT, MIN, MAX, etc. In this tutorial, I'm going to give some examples of how to use aggregate functions with Sequelize.js.

Database Models and Assocations

For this tutorial, first I define two simple models Item and SaleItem. Each models represent a real table in the database. Item stores the details of items. SaleItem stores data of item sales, with each record represents a sale for an item. For each sales, user can buy multiple amounts of an item, stored in amount column.

models/Item.js

  const mappings = {
    id: {
      type: Sequelize.UUID,
      primaryKey: true,
      defaultValue: Sequelize.DataTypes.UUIDV4,
    },
    name: {
      type: Sequelize.TEXT,
      allowNull: false,
    },
    stock: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },
    price: {
      type: Sequelize.DECIMAL,
      allowNull: false,
    },
    active: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: true,
    },
  };

models/SaleItem.js

  const mappings = {
    id: {
      type: Sequelize.UUID,
      primaryKey: true,
      defaultValue: Sequelize.DataTypes.UUIDV4,
    },
    itemId: {
      type: Sequelize.UUID,
      allowNull: false,
    },
    itemPrice: {
      type: Sequelize.TEXT,
      allowNull: false,
    },
    amount: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },
    cancelled: {
      type: Sequelize.BOOLEAN,
      defaultValue: false,
    },
  };

A SaleItem record belongs to an Item record and each Item may have multiple SaleItem records. We need to define the association.

models/associations.js

  const Item = require('./Item').getModel();
  const SalesItem = require('./SaleItem').getModel();
  
  SalesItem.belongsTo(Item, { foreignKey: 'itemId', targetKey: 'id' });
  Item.hasMany(SalesItem, { foreignKey: 'itemId', targetKey: 'id' });

MIN Example

In order to find the price of the cheapest item, we can use MIN clause on price column of Item table.

  exports.getMinPrice = () => Item.findAll({
    attributes: [[sequelize.fn('min', sequelize.col('price')), 'minPrice']],
  });

It generates the following query.

  SELECT min("price") AS "minPrice" FROM "Items" AS "Item";

The above example returns result in the following format.

  [ Item {
      dataValues: { minPrice: '5000000' },
      _previousDataValues: { minPrice: '5000000' },
      _changed: {},
      _modelOptions: 
       { timestamps: true,
         validate: {},
         freezeTableName: false,
         underscored: false,
         underscoredAll: false,
         paranoid: false,
         rejectOnEmpty: false,
         whereCollection: null,
         schema: null,
         schemaDelimiter: '',
         defaultScope: {},
         scopes: [],
         indexes: [Array],
         name: [Object],
         omitNull: false,
         sequelize: [Object],
         hooks: [Object],
         uniqueKeys: {} },
      _options: 
       { isNewRecord: false,
         _schema: null,
         _schemaDelimiter: '',
         raw: true,
         attributes: [Array] },
      __eagerlyLoadedAssociations: [],
      isNewRecord: false } ]

To make the result more readable, add raw: true as the argument.

  exports.getMinPrice = () => Item.findAll({
    attributes: [[sequelize.fn('min', sequelize.col('price')), 'minPrice']],
    raw: true,
  });

Here's the result with raw: true.

 [ { minPrice: '5000000' } ]

MAX Example

The query for finding the price of the most expensive item is very similar to the query for finding the price of the cheapest item, just replace MIN with MAX

  exports.getMaxPrice = () => Item.findAll({
    attributes: [[sequelize.fn('max', sequelize.col('price')), 'maxPrice']],
    raw: true,
  });

It generates the following query.

  SELECT max("price") AS "maxPrice" FROM "Items" AS "Item";

COUNT Example

Using COUNT clause, we can find how many times each items have been sold. This is not the total number of item sold. In other word, we ignore the value of amount column. For this case, we simply count how many times an item appear in the SaleItem table

  exports.getItemSaleCount = () => SaleItem.findAll({
    attributes: ['itemId', [sequelize.fn('count', sequelize.col('itemId')), 'count']],
    group : ['SaleItem.itemId'],
    raw: true,
    order: sequelize.literal('count DESC')
  });

It generates the following query.

  SELECT "itemId", count("itemId") AS "count" FROM "SaleItems" AS "SaleItem" GROUP BY "SaleItem"."itemId" ORDER BY count DESC;

SUM Example

If we want to get the list of best seller items, we need to sum up the value of amount column by using SUM clause.

  exports.getBestSellerItems = () => SaleItem.findAll({
    attributes: ['itemId', [sequelize.fn('sum', sequelize.col('amount')), 'total']],
    group : ['SaleItem.itemId'],
    raw: true,
    order: sequelize.literal('total DESC')
  });

It generates the following query.

  SELECT "itemId", sum("amount") AS "total" FROM "SaleItems" AS "SaleItem" GROUP BY "SaleItem"."itemId" ORDER BY total DESC;

Aggregate + Populate Example

Like the previous example, but now we also populate the Item detail.

  exports.getBestSellerItems = () => SaleItem.findAll({
    attributes: [[sequelize.fn('sum', sequelize.col('amount')), 'total']],
    include : [
      {
        model : Item,
      }
    ],
    group : ['Item.id'],
    raw: true,
    order: sequelize.literal('total DESC')
  });

It generates the following query.

  SELECT sum("amount") AS "total", "Item"."id" AS "Item.id", "Item"."name" AS "Item.name", "Item"."stock" AS "Item.stock", "Item"."price" AS "Item.price", "Item"."active" AS "Item.active", "Item"."createdAt" AS "Item.createdAt", "Item"."updatedAt" AS "Item.updatedAt" FROM "SaleItems" AS "SaleItem" LEFT OUTER JOIN "Items" AS "Item" ON "SaleItem"."itemId" = "Item"."id" GROUP BY "Item"."id" ORDER BY total DESC;

That's how to use aggregate functions in Sequelize.js