Search code examples
sumsequelize.jswhere-clausehavinggroup

Sequelize.js : Sum with condition and group by


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


Solution

  • 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']
            ]
        });