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 Reporting with SQL Aggregate and Numeric Functions Getting the Grand Total

Getting Keywords Mixed Up! GROUP BY/ORDER BY & WHERE/HAVING

Hi all, I'm working through Andrew's Reporting SQL course, and the more we dive into SQL the more shaky I get in my understanding of when and how we use GROUP BY vs. ORDER BY and WHERE vs. HAVING. Can anyone explain using a real-world relational example, like I don't know, puppies or ice cream or something that to explain a situation where we use one keyword over the other? Thanks so much for helping to clear up my confusion!

**Corrected syntax from HAVE to HAVING 4/1

Erik L
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Erik L
Full Stack JavaScript Techdegree Graduate 19,470 Points

I feel the same way, I pretty much understood the entire SQL Basics course, but in this course I feel like many keywords were introduced in the same video, also I wish more examples were done in the video to better understand the various keywords introduced here

1 Answer

Steven Parker
Steven Parker
231,184 Points

I think you mean HAVING (not HAVE). HAVING is a way to filter results based on something pertaining to a group. It is only used with GROUP BY. On the other hand, WHERE is a way to filter results based on something pertaining to the individual items. ORDER BY is just a way to sort the final results, after any filtering is done.

For some examples, let's say you have dogs in a database, for each you have a name, age, and breed. Now if you wanted to see all the entries for a particular breed ("pug" for example), you might do this:

SELECT * from dogs WHERE breed = 'pug';

But if you wanted to see how many dogs you had of each breed, listing each breed only once, you might do this:

SELECT breed, count(*) AS total from dogs GROUP BY breed;

Then, if you wanted to do the same thing, but only list the breeds where you had exactly 10 dogs of that type:

SELECT breed, count(*) AS total from dogs GROUP BY breed HAVING count(*) = 10;

And finally, if you wanted that same list sorted by breed (alphabetically):

SELECT breed, count(*) AS total from dogs GROUP BY breed HAVING count(*) = 10 ORDER BY breed;

So does that help?

Philip Rurka
Philip Rurka
8,633 Points

It helped me with HAVING and WHERE. Thanks.

Very good example!

Thanks

Justin Molyneaux
Justin Molyneaux
13,329 Points

Well explained examples. Thank you Steven!