Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialYingzi Huang
3,216 PointsTask #1: How to count number of outstanding books each patron has and add this "COUNT" to a third column in same query?
In the practice problems for this section, after finishing the query for selecting all patrons with outstanding books, I can display the result with or without "DISTINCT" to show which patrons still needs to return books.
SELECT DISTINCT first_name, email FROM patrons
INNER JOIN loans ON patrons.id = loans.patron_id WHERE returned_on IS NULL
Is there a method within the same query to "COUNT" the number of books per patron and write the result in the 3rd column?
I have tried using "GROUP BY" and "COUNT" but am unable to narrow down to the correct solution, likely due to how NULL entries are involved in this calculation.
1 Answer
Steven Parker
231,269 PointsYou just need to add a count column and a GROUP BY
clause.
Your WHERE
clause is fine as is. But you won't use DISTINCT
when you're grouping:
SELECT first_name, email, count(*) AS books -- added 3rd column
FROM patrons
INNER JOIN loans ON patrons.id = loans.patron_id
WHERE returned_on IS NULL
GROUP BY first_name, email -- added group clause
Yingzi Huang
3,216 PointsYingzi Huang
3,216 PointsThanks for clarifying, Steven! This logic makes perfect sense.