02.sql

CLUB -> Defines a CLUB
----
club (varchar)

MEMBER -> Holds member information for CLUBs
------
club (varchar refs CLUB)
id (customer id number, refs another table, I don't think this is neccesary to solve the problem)

PURCHASE -> Holds all purchases
--------
club (refs CLUB)
id (customer id, not sure if needed)
title (varchar, refs a BOOKS table, title of book purchased)
year (year the purchased book was written)
qty (int, amount purchased)

Offer -> Holds pricing info for clubs
-----
club (refs CLUB)
title (varchar, refs a BOOKS table, title of book purchased)
price (int)

Book -> info for books
-----
title (varchar, title of book purchased)
year (year the purchased book was written)


SELECT
	P.club,
	memberCount.members,
	titleCount.titles,
	SUM(O.Price * P.qnty) AS sales
FROM 
	purchase P
JOIN offer O ON 
	O.club = P.club AND P.title = O.title
JOIN (
	SELECT M.club, COUNT(*) AS members
	FROM member M
	GROUP BY M.club
) AS memberCount ON 
	memberCount.club = P.club
JOIN (
	SELECT P2.club, COUNT(*) AS titles
	FROM purchase P2, book B
	WHERE P2.title = B.title AND P2.year = B.year
	GROUP BY P2.club
) AS titleCount ON
	titleCount.club = P.club
GROUP BY
	--SUM(O.Price * P.qnty), -- Invalid use of aggregate function
	--sales,	-- not valid in the context it was used
	P.club,
	memberCount.members,
	titleCount.titles;

SELECT
    P.club,
    memberCount.members,
    titleCount.titles,
    SUM(O.Price * P.qnty) AS sales
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
JOIN (
    SELECT P2.club, COUNT(*) AS titles
    FROM yrb_purchase P2, yrb_book B
    WHERE P2.title = B.title AND P2.year = B.year
    GROUP BY P2.club
) AS titleCount ON
    titleCount.club = P.club
GROUP BY
    --SUM(O.Price * P.qnty), -- Invalid use of aggregate function
    --sales,    -- not valid in the context it was used
    P.club,
    memberCount.members,
    titleCount.titles;