Node.js - How to get sum and avg of column using sequelize?

Question

In node.js, I want to get sum of one column which has STRING dataType and value is like "367793582429824".

I just tried like as mentioned below but its throwing an error like SequelizeDatabaseError: column "assetamount" does not exist

[
 sequelize.fn("sum", sequelize.col("assetAmount")), "totalAssetAmount",
],

Model is,

module.exports = (sequelize, DataTypes) => {
 const items = sequelize.define("Items", {
  id: {
    allowNull: false,
    type: DataTypes.UUID,
    primaryKey: true,
    autoIncrement: true,
  },
  assetAmount: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  createdAt: {
    allowNull: false,
    type: Sequelize.DATE,
  },
  updatedAt: {
    type: Sequelize.DATE,
    allowNull: false,
  },
 });
};

Also I want to calculate an average of this column, how can I get SUM and AVG of this column?

Answer 1

Since your dataType is in STRING and SUM or AVG is a numeric function, first you need to cast the column into integer.

const { Sequelize } = require('sequelize')
let resp = await model.Item.findAll({
  attributes: [
    [Sequelize.fn("SUM", Sequelize.cast(Sequelize.col("assetAmount"), 'integer')), "totalAssetAmount"], 
    [Sequelize.fn('AVG', Sequelize.cast(Sequelize.col('assetAmount'), 'integer')), 'avgAssetAmount']
  ]
});

** Note that if you have non-numeric data in the column, this SQL will fail. For example, if you have 1 row with assetAmount = 'hello', then this SQL will raise an exception

Some mitigation ideas:

• Filter out all non-numeric rows with where option.
• Use validate to enforce having all numeric data in the column
• Modify the DB's schema to numeric column