2

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

Finance Table I want to get this result:

[
 {
  '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

AleOjeda
  • 38
  • 5

1 Answers1

1

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']
        ]
    });
Artash Mardoyan
  • 121
  • 4
  • Thank you so much for your quick answer, it was really useful. The solution: group option first. I quit the " in the columns,adapted it and success. So the final solution will be const finances = await Finance.findAll({ group: ['month', 'year'], attributes: [ 'year', 'month', [Sequelize.literal(`SUM(CASE WHEN is_paid THEN agents_commission ELSE 0 END)`), 'commission_paid_sum'], [Sequelize.literal(`SUM(CASE WHEN is_paid THEN 0 ELSE agents_commission END)`), 'commission_notPaid_sum'] ] }); – AleOjeda Jul 14 '22 at 13:04