SQL (SQLite) queries to show all benefactors and their donation amounts.

all_inserts.sql

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

clear.sql

DELETE FROM benefactors;
VACUUM;

DELETE FROM donations;
VACUUM;

create.sql

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

select_with_anon_donations.sql

-- show all donations for 2015

Select b.name, SUM(d.amount) as "Amount"
FROM benefactors b
LEFT JOIN donations d ON b.id = d.benefactorId
Where d.year = 2015
GROUP BY b.id
UNION
SELECT IFNULL(benefactorId, "Anonymous") AS "Name", SUM(amount) as "Amount"
FROM donations d
Where d.benefactorId Is Null and d.year = 2015;

select_without_anon_donations.sql

-- show all donations for 2015 except anonymous donors

Select b.name, SUM(d.amount) as "Amount"
FROM benefactors b
LEFT JOIN donations d ON b.id = d.benefactorId
Where d.year = 2015
GROUP BY b.id