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 Calculating Averages

Lionel Pena
Lionel Pena
3,346 Points

AVG(<numeric column>) and WHERE

In the previous video, it was stated that HAVING is used in conjunction with Aggregate Functions instead of WHERE. However, this video states you can use WHERE with AVG(<numeric column>). I thought AVG was an Aggregate Function and from what I've searched online this is true. So shouldn't we be using HAVING or is HAVING only used along with GROUP BY?

1 Answer

Steven Parker
Steven Parker
231,184 Points

HAVING is not intended as a replacement for WHERE.

They really have different purposes, and yes, they can be used together. WHERE would still be used when you are filtering on any per-row condition. HAVING would only apply when you need to filter on a condition that can only be tested against an aggregated value.

When the video says "To find the average on a subset of records, you could add a where clause.", it just means that the WHERE clause would be used to obtain the record subset (the per-row condition) that the average would then be applied to.

When constructing a query, a WHERE clause will be placed before a GROUP BY, and HAVING (if used) would be placed after it.