1

I have a donations table as follows.

Donations Table

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

Members Table

| id | name   | team_id   |
|----|--------|-----------|
| 01 |  tom   |         02|
| 02 |  jerry |         01|
| 03 |  jane  |         01|

Team Table

| id | name   |
|----|--------|
| 01 |  TeamA |
| 02 |  TeamB |

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

| amount | amount    |
|--------|-----------|
|  TeamA |       3000|
|  TeamB |        500|

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

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

This is not a duplicate of this question I've asked before

Muljayan
  • 3,588
  • 10
  • 30
  • 54

1 Answers1

1

You should be able to handle it in the mysql side:

SELECT
    MAX(t.name) AS TeamName,
    SUM(d.amount) AS TotalAmount
FROM team t
INNER JOIN members m ON t.id = m.team_id
INNER JOIN donations d ON m.id = d.member_id
GROUP BY t.id
;
Nae
  • 14,209
  • 7
  • 52
  • 79