I'm a biginner using Sequelize.
I spend hours trying to get the following code runs but I can't
I quit some columns from the table for better understanding.
Finance table
[
{
'month': 11,
'year': 2021,
'commission_paid_sum': 2700,
'comission_notPaid_sum':2500,
},
{
'month': 12,
'year': 2021,
'commission_paid_sum': 0,
'comission_notPaid_sum':1000,
},
{
'month': 1,
'year': 2022,
'commission_paid_sum': 2000,
'comission_notPaid_sum':0,
},
]
I tried:
1- adding a attribute but I don't get how to add the "where/having condition" in the fn Sum
db.Finanzas.findAll({
attributes: ['mes', 'ano','is_paid',
[Sequelize.fn('sum', Sequelize.col('agents_commission')), 'commission_paid_sum'],
[Sequelize.fn('sum', Sequelize.col('agents_commission')), 'comission_notPaid_sum'],
],
group: [ 'month' , 'year'],
I've tried also using the literal feature, but in this, I wasn't able to split them by month/year in the result.
If you think that there was an alternative option, I'll be happy to heard about that.
Thanks in advance!
Alejandro
Try it.
const { literal } = require('sequelize');
const finances = await Finance.findAll({
group: ['month', 'year'],
attributes: [
'year',
'month',
[literal(`SUM(CASE WHEN "is_paid" THEN "agents_commission" ELSE 0 END)`), 'commission_paid_sum'],
[literal(`SUM(CASE WHEN "is_paid" THEN 0 ELSE "agents_commission" END)`), 'commission_notPaid_sum']
]
});