-- Aggregate queries summarize subsets of records based on the GROUP BY clause
-- Each field SELECTed must either appear in GROUP BY or be an 'aggregate' operator
-- Aggregate operators are SUM(), AVG(), MIN(), MAX() and COUNT()

-- List total donation amounts by year
select year#, sum(amount) as totalgifts
	from gift
	group by year#;

-- Trick of the trade: Sometimes you group by things that don't help grouping
-- Show donors (donor# and name) and the number of years they've donated
-- Note that donor# is ambiguous (part of two tables) so precede with table name to clear up
select donor.donor#, CONCAT(dfname, ' ', dlname) as donor, count(year#) as #yearsdonated
	from donor inner join gift
		on donor.donor# = gift.donor#
	group by donor.donor#, dlname, dfname;

-- Remember, sometimes we to see everyone, not just those with activity; also may need some particular order
select donor.donor#, concat(dfname, ' ', dlname) as donor, coalesce(count(year#),0) as #yearsdonated
	from donor left outer join gift
		on donor.donor# = gift.donor#
	group by donor.donor#, dlname, dfname
	order by dlname, dfname;

-- The HAVING clause filters aggregate values
-- Donors who've donated in at least two years
select donor.donor#, CONCAT(dfname, ' ', dlname) as donor, count(year#) as #yearsdonated
	from donor inner join gift
		on donor.donor# = gift.donor#
	group by donor.donor#, dlname, dfname
	having count(year#) >= 2;

-- Remember, WHERE filters individual records
-- Show donors and number of years their donations exceeded $1000
select donor.donor#, CONCAT(dfname, ' ', dlname) as donor, count(year#) as #yearsdonated
	from donor inner join gift
		on donor.donor# = gift.donor#
	where amount > 1000
	group by donor.donor#, dlname, dfname

-- WHERE and HAVING can be combined
-- Show donors who have contributed at least $1000 in at least two years
-- p.s. some queries may return nothing & still might be correct
select donor.donor#, CONCAT(dfname, ' ', dlname) as donor, count(year#) as #yearsdonated
	from donor inner join gift
		on donor.donor# = gift.donor#
	where amount >= 1000
	group by donor.donor#, dlname, dfname
	having count(year#) >= 2

-- Sub-queries can help when you need to compare an aggregate to another
-- Show donors, year, and amount where donors have contributed more that the average amount across all donors & years
select donor.donor#, CONCAT(dfname, ' ', dlname) as donor, year#, amount
	from donor inner join gift
		on donor.donor# = gift.donor#
	where amount > (select avg(amount) from gift);

-- EXISTS & NOT EXISTS
-- Show donors who have made a gift
select donor.donor#, CONCAT(dfname, ' ', dlname) as donor
	from donor
	where exists(select donor# from gift where donor# = donor.donor#)

-- Show donors who have not made a gift
select donor.donor#, CONCAT(dfname, ' ', dlname) as donor
	from donor
	where not exists(select donor# from gift where donor# = donor.donor#)

-- Joins can involve more that two tables
-- list year, annual donation goal, donor, CONCAT(dfname, ' ', dlname) as donor, amount, and amount as percent of annual goal for all donations
select Year.year#, yeargoal, donor.donor#, CONCAT(dfname, ' ', dlname) as donor,amount, amount / yeargoal as pctofgoal
	from  donor INNER JOIN Gift ON Donor.donor# = Gift.donor#
				INNER JOIN Year ON Gift.year# = Year.year#

-- Query designer as QBE (query by example)

--SELECT        Year.year#, SUM(Donor.donor#) AS Expr1
--FROM            Donor INNER JOIN
--                         Gift ON Donor.donor# = Gift.donor# INNER JOIN
--                         Year ON Gift.year# = Year.year#
--GROUP BY Year.year#

-- Beware the sophomore join
-- List donor# and year# for all donations

SELECT        Donor.donor#, Year.year#
FROM            Year AS Year_1 CROSS JOIN
                         Donor



