0

I want to pull a list of units that have an overall rating of less than 4. The first query returns 1760 rows with data that seems to check out when cross referenced. The second query returns only 434 rows. Why would this be? I don't fully understand the "Group By" and "Having" clauses, so I'm guessing it has something to do with those.

'''
SELECT u.unitid
,AVG(r.overall) AS AverageRating
, u.ratingcount

FROM reviews r

LEFT JOIN units u
ON u.unitid = r.unitid

-- only retrieve active, non-terminated units set to display
WHERE u.active = 1
AND u.display = 1
AND u.terminated = 0

GROUP BY u.unitid

HAVING AverageRating < 4
;
```
```
SELECT u.UnitID
, u.rating
, u.ratingcount

from units u

WHERE u.rating < 4

-- only retrieve active, non-terminated units set to display
and u.active = 1
and u.display = 1
and u.terminated = 0
;
```
AliasHendrickson
  • 9
  • 1

1 Answers1

0

Your first query looks at all the records and calculates average rating by Unit ID, and then the HAVING clause limits the final result to records where the average rating by Unit ID is less than 4.

Your second query lists all records where the rating is less than 4, and that's all it does. It doesn't average anything.

Here are a couple passable tutorials I found with a quick Google:

HAVING filters aggregate values like SUM, AVG, MIN, MAX, etc. It's similar to WHERE, but WHERE only filters non-aggregated values. You can do this:

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
HAVING AVG(Rating) < 4 -- Good: HAVING is for filtering on aggregate values

But you can't do this (only difference from above is WHERE instead of HAVING in the last line):

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
WHERE AVG(Rating) < 4 -- Bad: WHERE is for the raw values, before they're aggregated

Keep at it. You'll get there :)

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69