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 trialEthan Martin
Courses Plus Student 1,883 PointsIdentifying the Patrons with outstanding Book Loans and the Count of their Outstanding Books
SELECT patrons.first_name, COUNT(loans.patron_id) AS "Books Outstanding", patrons.email FROM patrons INNER JOIN loans ON patrons.id = loans.patron_id GROUP BY patrons.first_name HAVING returned_on IS NULL;
first_name - Books Outstanding - email
Alena 37 alena.holligan@teamtreehouse.com
Andrew 19 andrew.chalkley@teamtreehouse.com
Dave 18 dave.mcfarland@teamtreehouse.com
This is what gets returned but I know this is wrong
Alena should =4 Dave should = 4 Andrew should = 4
What am I doing wrong. I want to select the NAME, COUNT, and their EMAIL. It is important to count only the IDs where the RETURN DATE is empty.
5 Answers
KRIS NIKOLAISEN
54,971 PointsYou should use a WHERE clause instead of HAVING
SELECT patrons.first_name, COUNT(loans.patron_id) AS "Books Outstanding", patrons.email FROM loans INNER JOIN patrons ON patrons.id = loans.patron_id WHERE loans.returned_on IS NULL GROUP BY loans.patron_id
Ikechukwu Arum
5,337 PointsSELECT patrons.first_name AS "First_Name", patrons.email AS "Emails", COUNT(loans.patron_id) AS "COUNT" FROM loans INNER JOIN patrons ON patrons.id = loans.patron_id GROUP BY loans.patron_id,returned_on HAVING loans.returned_on IS NULL;
Ingenious Courses
5,353 PointsHi @ikechukwumerije! Why did you use FROM loans
first instead of FROM patrons
?
Ikechukwu Arum
5,337 PointsI think you tagged the wrong person but the question is asking for outstanding book loans so I would imagine you'd look at the loans table to get the information
Ingenious Courses
5,353 Pointshaha sorry and thanks1
R S
2,255 PointsHaving and where are both filtering keywords. The key to when to use which keyword for filtering is WHEN you want to filter. If you want to filter the records BEFORE you group them, use the WHERE keyword. If you want to filter the result set AFTER you group the records then use the HAVING keyword In this case you want to filter out those loans that were already returned BEFORE you group those patrons with outstanding loans...Why would you want to group patrons that don't even have any outstanding loan?!
Ethan Martin
Courses Plus Student 1,883 PointsEthan Martin
Courses Plus Student 1,883 PointsThank you!