CODEWITHSUNDEEP
sqlcount
Lechucico
Lechucico

Reputation: 2112

SQL Show value that appears more times and how many times

I'm trying to show the value that appears more times from a movies table.

For example:

movie_id, tag_id, score
1         1       4
1         3       5
2         1       3
3         2       4
3         3       5

Result:

tag_id, times
1       2
3       2
2       1

That table has the following columns: {movie-id, tag-id, score}.

How I can retrieve the tag-id that appears more times and how many times?

I've tried the following but it shows the same number for each tag-id:

SELECT tagId, COUNT(tagId) AS ocurrence FROM scores GROUP BY tagId ORDER BY ocurrence DESC

Upvotes: 0

Views: 36

Answers (2)

ollaw
ollaw

Reputation: 2213

SELECT tagId, COUNT(tagId) FROM scores GROUP BY (tagID) HAVING COUNT(tagId) >= ALL 
    (SELECT COUNT(tagId) FROM scores GROUP BY (tagId))

This will also work.

Upvotes: 0

lnjblue
lnjblue

Reputation: 150

I think you're looking for:

SELECT TAGID, COUNT(TAGID) 
FROM TABLENAME
GROUP BY TAGID
ORDER BY COUNT(TAGID) 

--or you could do a having clause where COUNT(TAG-ID) > 1

Upvotes: 1

Related Questions