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

It would be very simple in a sql way, `select member_id,sum(amount) from Donations group by member_id` , but you want implement this in a `node.js` way? — Shawn.X, Jun 11 '19 at 07:17
Yes i am aware that doing it using pure sql queries is easier but i hope to have uniformity in my app and since im using sequelize ORM i'd like to implement this functionality using sequelize as well. — Muljayan, Jun 11 '19 at 07:19

score 55 · Answer 1 · edited Sep 08 '20 at 12:26

55

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

edited Sep 08 '20 at 12:26

Muljayan

3,588
10
30
54

answered Jun 11 '19 at 09:24

Hasantha Lakshan

551
3
3

4

you should add text if possible to explain your answer – Kevin Jun 11 '19 at 09:38
When I do this, I can't access totalAmount. It is an object with `dataValues` etc. How do you get the number? – ajbraus Apr 11 '21 at 02:16

score 4 · Answer 2 · edited Nov 20 '21 at 08:38

4

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

edited Nov 20 '21 at 08:38

Oli

9,766
5
25
46

answered Nov 19 '21 at 16:35

Alejandro Saenz

41
1

Finding sum and grouping in sequelize

2 Answers2

Linked