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 DISTINCT
P.club, sum(O.Price * P.qnty) AS sales
FROM
yrb_offer O, yrb_purchase P
WHERE
O.club = P.club
GROUP BY P.club;

SELECT DISTINCT 
    P.club, sum(O.Price * P.qnty) AS sales
FROM 
    yrb_offer O, yrb_purchase P
WHERE 
    O.club = P.club
GROUP BY P.club;

SELECT DISTINCT 
    P.club, sum(O.Price * P.qnty) AS sales
FROM 
    offer O, purchase P
WHERE 
    O.club = P.club AND P.title = O.title
GROUP BY P.club;



CLUB            SALES
--------------- ---------------------------------
Basic                                     1447.93
CAA                                       2328.69
CARP                                       841.60
Guelph Club                                156.44
Oprah                                     1666.07
Readers Digest                            1234.50
UofT Club                                  394.49
Waterloo Club                             1358.58
YRB Bronze                                  62.85
YRB Gold                                  1140.00
YRB Silver                                 409.05
York Club                                 1063.79

WITH num_sales AS (
    SELECT DISTINCT 
        P.club, sum(O.Price * P.qnty) AS sales
    FROM 
        offer O, purchase P
    WHERE 
        O.club = P.club AND P.title = O.title
    GROUP BY P.club
)
SELECT 
    C.club,
    count(*) AS #MEMBERS,
    S.sales AS #SALES
FROM 
    club C,
    member M,
    num_sales S
WHERE C.club = M.club AND S.club = C.club
GROUP BY C.club;


 An expression starting with "SALES" specified in a SELECT clause,
HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or
it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column
function and no GROUP BY clause is specified.

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;