CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Muljayan
Muljayan

Reputation: 3886

Finding sum and grouping in sequelize

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

Upvotes: 23

Views: 62420

Answers (2)

Hasantha Lakshan
Hasantha Lakshan

Reputation: 591

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

Upvotes: 59

Alejandro Saenz
Alejandro Saenz

Reputation: 81

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

Upvotes: 8

Related Questions