Sequelize: How to multiply column into an aggregate function

user_1263145 0
Viewed 3613

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]
})
sequelize.js node.js mysql
2 Answers

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]
})
user_7526200
Related