02.sql

purchase
=======
club
title
year

book
====
title 
year

WITH distinctPurchasedBooks (title, year, num) AS (
	SELECT DISTINCT P.title, P.year, count(*)
	FROM yrb_purchase P
	GROUP BY P.title, P.year
)
SELECT
	P.club,
	SUM(O.Price * P.qnty) AS sales,
	titleCount.titles,
	memberCount.members
FROM 
	yrb_purchase P
JOIN yrb_offer O ON 
	O.club = P.club AND P.title = O.title
JOIN (
	SELECT M.club, COUNT(*) AS members
	FROM yrb_member M
	GROUP BY M.club
) AS memberCount ON 
	memberCount.club = P.club

	-- Count distinct titles
JOIN (
	SELECT DISTINCT P2.club, D.num AS TITLES
	FROM yrb_purchase P2, yrb_book B, distinctPurchasedBooks D
	WHERE D.title = P2.title AND D.year = P2.year
	GROUP BY P2.club, D.num
) AS titleCount ON
	titleCount.club = P.club
	
	-- /Count distinct titles
GROUP BY
	P.club,
	memberCount.members,
	titleCount.titles
ORDER BY
	2 DESC,
	P.club;

count(DISTINCT B.title) doesn't work because two books can have the same title. I require something more like count(DISTINCT (B.title, B.year))