Sequelize sum between two columns in model

Question

How can I create a query like this with sequelize ?

SELECT name, region, SUM(((COALESCE(base_income, 0) + COALESCE(user_taxes, 0))) AS total_sal FROM user GROUP BY name, region;

Answer 1

OK, so following is the solution for above RAW sql query.

  user.findAll({
  attributes: ['name', 'region', [sequelize.fn('SUM', (sequelize.fn('COALESCE', (sequelize.col('base_income')), 0), sequelize.literal('+'), sequelize.fn('COALESCE', (sequelize.col('user_taxes')), 0))), 'total_sal']],
  group: ['name', 'name']})

We have to use sequelize.literal inorder to place any operator between query. Hope this helps.

Answer 2

Although it is an old post, I ran to this now twice. So I'd like to share my solution:

user.findAll({ 
  attributes: [ 'name', 'region',
    [ sequelize.literal(
        'COALESCE(base_income, 0) + COALESCE(user_taxes, 0)'
      ), 'total_sal'
    ]
  ],
  group: ['name', 'name']
})

Answer 3

Despite not finding in the documentation the use as attributes. The sequelize.where function can be a good solution, as it allows the use of sequelize.col for case of associations in models.

User.findAll({
  attributes: [
    'name',
    'region',
    [
      Sequelize.fn(
        'SUM',
        Sequelize.where(Sequelize.col('base_income'), '+', Sequelize.col('user_taxes'))
      ),
      'total_sal',
    ],
  ],
  group: ['name', 'region'],
})