How to find benefactor names and sum of their contributions for an year including anonymous benefactors whose ids are NULL

I have an SQL query that need to be solved.
The question is as follows

A welfare organization has been receiving donations from benefactors over the years. The data is stored in the following tables:

CREATE TABLE benefactors (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE donations (
  id INTEGER PRIMARY KEY,
  amount INTEGER NOT NULL,
  year INTEGER NOT NULL,
  benefactorId INTEGER REFERENCES benefactors(id)
);

INSERT INTO benefactors(id, name) VALUES(1, 'Phil');
INSERT INTO benefactors(id, name) VALUES(2, 'Nicholas');
INSERT INTO benefactors(id, name) VALUES(3, 'William');

INSERT INTO donations(id, amount, year, benefactorId) VALUES(1, 2000, 2014, 1);
INSERT INTO donations(id, amount, year, benefactorId) VALUES(2, 2800, 2015, 1);
INSERT INTO donations(id, amount, year, benefactorId) VALUES(3, 900, 2015, 1);
INSERT INTO donations(id, amount, year, benefactorId) VALUES(4, 1200, 2015, 2);
INSERT INTO donations(id, amount, year, benefactorId) VALUES(5, 3200, 2015, null);
INSERT INTO donations(id, amount, year, benefactorId) VALUES(6, 4000, 2015, null);
INSERT INTO donations(id, amount, year, benefactorId) VALUES(7, 2400, 2016, 2);

Some donations are received from anonymous sources (benefactorId is null).

Write a query that returns:

  1. Benefactor names.
  2. Sum of their contributions for the year 2015.

Benefactors who haven’t been able to contribute in the year 2015, should also be returned with their contribution as 0. All anonymous contributions should be summed under the name ‘Anonymous’. If there are no anonymous contributions for the year 2015, no rows with the name ‘Anonymous’ should be returned.

I have tried using left join but not able to understand how to use Union and Case statements along with this to solve the problem. I need Anonymous contributors and name of those who have made 0 donations too for the year 2015.

This is what I tried.

SELECT IFNULL(b.name,'Anonymous') AS Name, IFNULL(SUM(d.amount),0) AS 'Amount'
FROM benefactors b
LEFT JOIN donations d
ON b.id = d.benefactorId 
WHERE d.year = 2015
GROUP BY b.id

I get only these as the result

NameAmount
Phil3700
Nicholas1200

whereas correct result is

NameAmount
Phil3700
Nicholas1200
Anonymous7200
William0

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Rather than 3 queries, You an achieve the desired result in 2 queries only –

SELECT IFNULL(b.name,'Anonymous') AS Name, IFNULL(SUM(d.amount),0) AS 'Amount'
FROM benefactors b
LEFT JOIN donations d
ON b.id = d.benefactorId 
AND d.year = 2015
GROUP BY b.id

UNION

SELECT IFNULL(b.name,'Anonymous') AS Name, IFNULL(SUM(d.amount),0) AS 'Amount'
FROM benefactors b
RIGHT JOIN donations d
ON b.id = d.benefactorId 
WHERE d.year = 2015
GROUP BY b.id;

Demo.

Method 2

select b.name as Name, sum(coalesce(d.amount, 0)) as Amount
from benefactors b 
left join donations d
on b.id = d.benefactorId 
where d.year = 2015 or d.year is null
group by b.id

union 

select'Anonymous' as Name, sum(amount) as Amount 
from donations 
where benefactorId is null;

Method 3

Please try this

SELECT * FROM (
 SELECT IFNULL(name, 'Anonymous') as name, IFNULL(sum(amount), 0) as amount, IFNULL(t.year, 2015) as 'year'
 FROM (
   SELECT b.name, d.amount, d.year FROM benefactors b
   LEFT JOIN donations d ON d.benefactorId = b.id
   UNION SELECT b.name, d.amount, d.year FROM benefactors b
   RIGHT JOIN donations d ON d.benefactorId = b.id
 ) as t GROUP BY t.name, t.year
) as tt where tt.year = 2015;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x