PARTICIPATION ACTIVITY

3.4.8: Books with single author.

Modify the WHERE clause to use a subquery. The subquery uses a HAVING clause with COUNT() to select only book IDs that appear in one row of BookAuthor.

SELECT C.State, S.BookID, B.Title, SUM(S.Quantity) AS Quantity, SUM(S.UnitPrice * S.Quantity) AS TotalSales FROM Sale S INNER JOIN Customer C ON C.ID = S.CustID INNER JOIN Book AS B ON B.ID = S.BookID WHERE (C.State = 'CO' OR C.State = 'OK') AND MONTH(S.Date) = 2 AND YEAR(S.Date) = 2020 AND B.ID IN (SELECT BookID FROM BookAuthor GROUP BY BookID HAVING COUNT(*) = 1) GROUP BY C.State, S.BookID ORDER BY TotalSales DESC;