When to use Having?

1

Hi Experts!

Can someone please clarify when this query is used ' HAVING' instead of LIKE?

Below is the problem and the answer:

Run Query: Find the albums with 12 or more tracks.

SELECT AlbumId, COUNT(TrackId) as total_tracks
FROM Tracks
GROUP BY AlbumId HAVING total_tracks >= 12;

2

See SQL Tutorial.

3

I did, but I am confused. Thats why I have created a question.

4

HAVING filters the aggregates produced by the GROUP BY clause. (ie A sort of WHERE clause for aggregates.) eg

GROUP BY YourId
HAVING COUNT(*) > 5
GROUP BY YourId
HAVING SUM(Amount) > 10

Incidentally your example is not standard SQL as total_tracks does not exist until the SELECT clause which is logically evaluated after the HAVING clause

https://blog.sqlauthority.com/2020/11/18/sql-server-logical-processing-order-of-the-select-statement/

In standard SQL your example should be:

SELECT AlbumId, COUNT(TrackId) AS total_tracks
FROM Tracks
GROUP BY AlbumId
HAVING COUNT(TrackId) >= 12;

The optimizer in some versions of SQL will manage to evaluate HAVING total_tracks >= 12. I think Oracle 23c has just added this ability.

1 Like
5

Thank you for the explanation!
I am still confused although I have tried to read the link you have given.

"as total_tracks" is an alias in my example. Does it mean that you won't be able to select an alias for the Having clause??

6

Yes. Logically SELECT is evaluated after HAVING so the alias does not yet exist when the HAVING clause is evaluated.

To use an alias a nested SELECT, using either a CTE or derived table, would be needed:

WITH TotalCTE
AS
(
	SELECT AlbumId, COUNT(TrackId) AS total_tracks
	FROM Tracks
	GROUP BY AlbumId
)
SELECT AlbumId, total_tracks
FROM TotalCTE
WHERE total_tracks >= 12;

or

SELECT AlbumId, total_tracks
FROM
(
	SELECT AlbumId, COUNT(TrackId) AS total_tracks
	FROM Tracks
	GROUP BY AlbumId
) D
WHERE total_tracks >= 12;
1 Like
7

Thank you for the response!

What does "CTE" mean?

I am sure you are giving me a piece of insightful information, but it sounds too technical or advanced for me to digest what you are explaining.