SQL SUM values by DISTINCT column after JOIN

I’ve been searching the forums for a solution for hours now but I couldn’t quite find the one that is fitting for my problem.

I have two tables:

members
****************
id   name    city
1    John    Boston
2    Maria   Boston
3    Steve   London
4    Oscar   London
5    Ben     Singapore

donations
********************
member_id    amount
1            100
1            150 
2            300 
3            50
3            100
3            50
4            75
5            200

I’m trying to create an aggregated list of donations by cities. So I should get the sum of each members’ donations and sum that by each city.

So my end result should be something like this:

Result
*************
Boston    550
London    275
Singapore 200

This is the latest version of my query:

SELECT me.id, me.city, don.mySUM
   FROM members me
LEFT JOIN (SELECT member_id, SUM(amount) AS mySUM 
   FROM donations GROUP BY member_id) don 
ON don.member_id=me.id GROUP BY me.city ORDER BY mySUM DESC

But it’s obviously missing something because it aggregates the cities but only adds the sum of one users donations.

Could anybody please point me to the right direction?

Answer

If there is no donation information of a city then donation column will show null. To convert it to 0 you can use coalesce(SUM(d.amount),0).

Schema and insert statements:

create table members(id int,   name varchar(50),    city varchar(50));
insert into members values(1,    'John'    ,'Boston');
insert into members values(2,    'Maria'   ,'Boston');
insert into members values(3,    'Steve'   ,'London');
insert into members values(4,    'Oscar'   ,'London');
insert into members values(5,    'Ben'     ,'Singapore');

create table donations(member_id int,    amount int);
insert into donations values(1,            100);
insert into donations values(1,            150); 
insert into donations values(2,            300 );
insert into donations values(3,            50);
insert into donations values(3,            100);
insert into donations values(3,            50);
insert into donations values(4,            75);
insert into donations values(5,            200);

Query:

SELECT m.city, SUM(d.amount) as donations
FROM members m LEFT JOIN
     donations d
     ON d.member_id = m.id
GROUP BY m.city
ORDER BY city;

Output:

city	total_donations
Boston	550
London	275
Singapore	200

db<>fiddle here