在 sequelize 中查找总和和分组

2022-01-19 00:00:00 node.js mysql sequelize.js

我有一个如下的捐款表.

I have a donations table as follows.

捐款表

| id| amount | member_id |
|---|--------|-----------|
| 0 |   500  |         01|
| 1 |  1000  |         02|
| 2 |  2000  |         01|

如何查找 sum 并按成员 id 对表进行分组，如下所示.

How to find sum and group the table by member id as follows.

| amount | member_id |
|--------|-----------|
|  2500  |         01|
|  1000  |         02|

我尝试使用以下代码，但它似乎不起作用.

I tried to use the following code but it doesnt seem to work.

const salesValue = await DONATIONS.sum('amount', {
    group: 'member_id'
});

推荐答案

您必须使用 sequelize.fn 进行聚合.要定位金额列，您必须使用 sequelize.col 并将它们分组，您必须传递 group 选项.

You have to do an aggregation using sequelize.fn. To target the amount column you have to use sequelize.col and to group them you have to pass the group option.

const totalAmount = await DONATIONS.findAll({
        attributes: [
          'member_id',
          [sequelize.fn('sum', sequelize.col('amount')), 'total_amount'],
        ],
        group: ['member_id'],
      });

相关文章