Finding sum and grouping in sequelize

Question

I have a donations table as follows.

Donations Table

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

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

Answer 1

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

Answer 2

if you want to get the value directly add the property: raw: true

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