gistfile1.txt

SELECT *
FROM (
  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;
) data
ORDER BY sales