sqlzoo track join exercise

Question

I've found a great site to practice sql - http://sqlzoo.net. my sql is very weak that is why i want to improve it by working on the exercises online. But i have this one problem that I cannot solve. can you please give me a hand.

3a. Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up.

album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)

my answer is incorrect as i ran the query.

 select a.song, count(a.song) from track a, track b
 where a.song = b.song
a.album != b.album
group by a.song
having count(a.song) > 2

thanks in advance! :D

Answer 1

I realize this answer may be late but for future reference to anyone taking on this tutorial the answer is as such

SELECT track.song, count(album.title)
FROM album INNER JOIN track ON (album.asin = track.album)
GROUP BY track.song
HAVING count(DISTINCT album.title) > 2

Some things that my help you in your quest for this query is that what to group by is usually specified by the word each. As per the tip presented in the previous answers you want to select by distinct albums, SINCE it mentioned in the database description that album titles would be repeated when the two tables are joined

Answer 2

Your original answer is very close, with the GROUP BY and HAVING clause. What is wrong, is just that you don't need to join the track table against itself.

SELECT song, count(*)
FROM track
GROUP BY song
HAVING count(*) > 2

Another answer here uses COUNT(DISTNCT album), which is necessary only if a song can appear on an album more than once.