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

Gustavo Winter
PLUS
Gustavo Winter
Courses Plus Student 27,382 Points
Posted by Gustavo Winter
Gustavo Winter
Courses 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

Gustavo Winter
Gustavo Winter
Courses Plus Student 27,382 Points
Gustavo Winter
Gustavo Winter
Courses Plus Student 27,382 Points

Oh, i see why it is so important.

Without it the books belong only to one person. The groups separate the books who owns to each person.

I will keep the question online to help who has teh same doubt.

By the way, if i'm wrong, please correct me, otherwise let me know if my thought is right.

1 Answer

Elad Ohana
Elad Ohana
24,456 Points
Elad Ohana
Elad Ohana
24,456 Points

Hi Gustavo,

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   |    patron1@email.com        | book1 |      1
patron1   |    patron1@email.com        | book2 |      1
patron1   |    patron1@email.com        | book3 |      1
patron2   |    patron2@email.com        | book4 |      1
patron2   |    patron2@email.com        | book5 |      1

with grouping, you would get something more like :

(PATRON     |    EMAIL                    |    COUNT):
patron1     |    patron1@email.com        |       3
patron2     |    patron2@email.com        |       2

Hope that helps