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