Welcome to the Treehouse Community
Looking to learn something new?
Gustavo WinterCourses Plus Student 27,382 Points
Group By - SQL
Hi, i'm having trouble to understanding the function of GROUP BY on SQL.
If possible use this code to explain (with details please =D) why group by is so important.
SELECT first_name, email, COUNT(*) AS "Loan Count" FROM patrons INNER JOIN (SELECT patron_id FROM loans_south WHERE returned_on IS NULL UNION ALL SELECT patron_id FROM loans_north WHERE returned_on IS NULL) AS loans ON patrons.id = loans.patron_id GROUP BY patron_id -- ( < --HERE )
The code above is from HERE
Elad Ohana24,456 Points
Not sure what you mean by books belonging to 1 person. The reason you need to GROUP BY is in order to use aggregate functions such as count. So for example, let's say you had patron1 borrow book1, book2, and book3 and patron2 borrow book4 and book5. Without the group by function, you would get a result similar to this (i'm including book in the first one for clarification, even though it is not selected in the query):
(PATRON | EMAIL | BOOK | COUNT): patron1 | firstname.lastname@example.org | book1 | 1 patron1 | email@example.com | book2 | 1 patron1 | firstname.lastname@example.org | book3 | 1 patron2 | email@example.com | book4 | 1 patron2 | firstname.lastname@example.org | book5 | 1
with grouping, you would get something more like :
(PATRON | EMAIL | COUNT): patron1 | email@example.com | 3 patron2 | firstname.lastname@example.org | 2
Hope that helps