How to use HAVING with COUNT as aggregation function

Question

I need to display only genres which have at least two titles.

My Titles table:

Titles
------
TitleID, ArtistID, Title, StudioID, Genre

My Tracks table:

Tracks
------
TitleID, TrackNum, TrackTitle

This is my code so far:

select t.genre from titles t
join tracks tr
on (tr.titleid = t.titleid)
having count(tr.tracktitle) > 1;

I don't this the current code is answering the described question correctly.

I would have concerns about duplicate rows. Probably need to add a distinct inside the count(). Why don't you think it's correct? — UnhandledExcepSean
– UnhandledExcepSean, Commented Nov 20, 2013 at 21:13
Do you need the Tracks table in there at all? It sounds like you only need the Titles table for this. — Kyle Mueller
– Kyle Mueller, Commented Nov 20, 2013 at 21:14

Yosi Dahari · Accepted Answer · 2013-11-20 21:19:00Z

5

If you want to use HAVING with a real meaning, you should use GROUP BY

SELECT t.genre, count(tr.tracktitle) num_tracktitle
FROM titles t
    JOIN tracks tr
       ON (tr.titleid = t.titleid)
GROUP BY t.genre
HAVING num_tracktitle > 1;

edited Nov 20, 2013 at 21:19

answered Nov 20, 2013 at 21:13

Yosi Dahari

7,0495 gold badges26 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barmar Over a year ago

Use having num_tracktitle > 1 so you don't have to count twice.

Andrew Over a year ago

Sidenote: MySQL is optimised enough that it does not count twice.

Kyle Mueller · Accepted Answer · 2013-11-20 21:18:40Z

0

Why do you need the tracks table? It seems like you have everything you need in the title table.

SELECT
    TI.Genre,
    Count(TI.TitleID) As Titles
FROM
    Titles TI
GROUP BY
    TI.Genre
HAVING
    COUNT(TI.TItleID) > 1

answered Nov 20, 2013 at 21:18

Kyle Mueller

1161 silver badge3 bronze badges

Collectives™ on Stack Overflow

How to use HAVING with COUNT as aggregation function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related