CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Ahmed-Anas
Ahmed-Anas

Reputation: 5619

Sequelize: How to multiply column into an aggregate function

So basically what I want is:

select (table2.col1 * sum(table1.col1)) as myAggregate
from table1 
join table2 ON table2.id = table1.id

I've gotten this far but don't know how to add the multiplication:

Table1Model.findAll({

    attributes: [[Sequelize.fn('SUM', 'col1'), 'myAggregate']]
    include: [Table2Model]
})

Upvotes: 3

Views: 4862

Answers (2)

Arkanoid
Arkanoid

Reputation: 1195

I don't know if this is covered by Sequelize docs, but you could use Sequelize.where to accomplish this. You might need to change table names, but it would be roughly like this:

Table1Model.findAll({
  attributes: [
    [
      Sequelize.where(
        Sequelize.col('table2.col1'),
        '*',
        Sequelize.fn('SUM', Sequelize.col('col1')),
      ),
      'myAggregate',
    ]
  ],
  include: [Table2Model]
})

Upvotes: 0

Ahmed-Anas
Ahmed-Anas

Reputation: 5619

I ended up going for 'raw' mysql. Note that this won't be cross-database compatible. Here's what I did in code (this is mySQL)

    attributes: [
        [Sequelize.literal('(`Meetup.MeetupBusinessPercentages`.percentageCut * SUM(MeetupCharges.amount) / 100 )'), 'totalAmount']
    ],

Upvotes: 9

Related Questions