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?

CodePudding user response：

i just created same shema in squelize as urs and run query to get sum and average u can use this code its working at my end..

"use strict";
module.exports = (sequelize, DataTypes) => {
    var Item = sequelize.define(
        "Item",
        {
            assetAmount: {
                type: DataTypes.STRING,
                allowNull: false,
            },
        },
        {
            freezeTableName: true,
        }
    );
    return Item;
};

db data

here is my query for sum and avg

        let resp = await model.Item.findAll({
            attributes: [[
                model.sequelize.fn("sum", model.sequelize.col("assetAmount")), "totalAssetAmount",
            ], [model.sequelize.fn('AVG', model.sequelize.col('assetAmount')), 'avgRating']]
        });
console.log(resp)

Here is Response as u required

[
    {
        "totalAssetAmount": 1469134,
        "avgRating": 734567
    }
]

CodePudding user response：

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