Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

Gustavo Winter
PLUS
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

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

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