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

Rob B
Rob B
11,677 Points
Posted by Rob B
Rob B
11,677 Points

WHERE, GROUP BY, and HAVING

I'm trying to understand a way to visualize and understand the difference between these three clauses: WHERE is a filter for rows. HAVING is a filter for groups. GROUP BY orders things into groups.

I just don't understand how to visualize these groups, nor do I understand why WHERE vs HAVING display different results while using the same filter condition (e.g.; custOrders >= 20). (I just started a 20461C, Querying SQL, course)

2 Answers

Thomas Nilsen
Thomas Nilsen
14,957 Points
Thomas Nilsen
Thomas Nilsen
14,957 Points

WHERE and HAVING is very similar, BUT there is one big difference:

  • use WHERE when you want to filter individual rows

  • use HAVING when you want to filter on aggregations.

let say you have a table with two columns; PEOPLE and AGE

If I wanted to display only people who are older than 50, I would write:

SELECT * FROM PEOPLE
WHERE AGE > 50

BUT, let's say I wanted to group this (i.e how many people within each age) AND only display the groups with more than 10 people, we could write it like this:

SELECT
AGE
,COUNT(*) AS NUMBER_OF_PEOPLE
FROM PEOPLE

GROUP BY AGE
HAVING COUNT(*) > 10

You see the difference? In the last example we filter on an aggregated group.

Rob B
Rob B
11,677 Points
Rob B
Rob B
11,677 Points

Using HAVING is centered around aggregations then? In other words, WHERE is for more simplistic filters, whereas HAVING is for the more complex filters. Is that correct?

Thomas Nilsen
Thomas Nilsen
14,957 Points
Thomas Nilsen
Thomas Nilsen
14,957 Points

I'm not a fan of your last sentence. You can have pretty complex where filters (multiple or/and in/not in etc..)

But yeah - pretty much.

You want to filter on aggregations - you have to use HAVING

Otherwise, where is fine.

Rob B
Rob B
11,677 Points
Rob B
Rob B
11,677 Points

I like that second-to-last sentence best, basically summarizing that if you want to filter using aggregations, you have to use HAVING. Thank you very much. :)