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 trial

Databases Querying Relational Databases Joining Table Data with SQL Review and Practice

Posted by Yingzi Huang
Yingzi Huang
3,216 Points

Task #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
Steven Parker
231,268 Points
Steven Parker
Steven Parker
231,268 Points

You 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
Yingzi Huang
3,216 Points

Thanks for clarifying, Steven! This logic makes perfect sense.