How to get the maximum count, and ID from the table SQL

Question

**castID**
nm0000116
nm0000116
nm0000116
nm0000116
nm0000116
nm0634240
nm0634240
nm0798899

This is my table (created as a view). Now I want to list the castID which has the most count (in this case which is nm0000116, and how many occurences/count it has in this table ( should be 5 times) and I'm not quite sure which query to use

Please clarify your specific problem by providing the code you've tried. As it's currently written, it’s hard to tell exactly what you're asking. — Kermit, May 05 '14 at 16:24

potashin · Answer 1 · 2014-05-05T03:38:28.377

1

SELECT 
  MAX(E),
  castId
FROM 
 (SELECT COUNT(castId) AS E,castId FROM [directors winning movies list] GROUP BY castId) AS t

edited May 05 '14 at 03:38

answered May 05 '14 at 03:16

potashin

44,205
11
83
107

This is what I have entered: SELECT MAX(E), castID FROM (SELECT COUNT(caseID),castID AS E FROM `directors winning movies list` GROUP BY castID) BUUTT I get MySQL said: #1248 - Every derived table must have its own alias – user3601148 May 05 '14 at 03:22
Yes, have used quotation marks, #1248 - Every derived table must have its own alias – user3601148 May 05 '14 at 03:28
Now I get an error #1054 - Unknown column 't.castID' in 'field list' – user3601148 May 05 '14 at 03:30
#1054 - Unknown column 'castID' in 'field list' is still persisting – user3601148 May 05 '14 at 03:37
@user3601148 : My bad, assigned alias to another field.Check updated. – potashin May 05 '14 at 03:39

Charles Bretana · Answer 2 · 2014-05-05T03:30:59.347

1

try

Select CastId, count(*) countOfCastId
From table 
Group By CastId
Having count(*)
    = (Select Max(cnt)
       From (Select count(*) cnt
             From table
             Group By CastId) z)

edited May 05 '14 at 03:30

answered May 05 '14 at 03:23

Charles Bretana

143,358
22
150
216

heeyy thanks for that, could you tell me how I would be able to get the count too for the castID that is selected? – user3601148 May 05 '14 at 03:26
Yeah, just include `count(*)` in Select clause... I edited query. – Charles Bretana May 05 '14 at 03:30
I've been trying to create a view, but it's giving me an error: #1349 views slect contains a subquery in the from clause, do you have any suggestions? – user3601148 May 05 '14 at 06:31

score 0 · Answer 3 · edited May 23 '17 at 10:32

You could just return the topmost count using LIMIT:

SELECT   castID,
         COUNT(*) AS Cnt
FROM     atable
GROUP BY castID
ORDER BY Cnt DESC
LIMIT    1
;

However, if there can be ties, the above query would return only one row. If you want all the "winners", you could take the count from the above query as a scalar result and compare it against all the counts to return only those that match:

SELECT   castID,
         COUNT(*) AS Cnt
FROM     atable
GROUP BY castID
HAVING   COUNT(*) = (
                        SELECT   COUNT(*)
                        FROM     atable
                        GROUP BY castID
                        ORDER BY Cnt DESC
                        LIMIT    1
                    )
;

(Basically, same as Charles Bretana's approach, it just derives the top count differently.)

Alternatively, you could use a variable to rank all the counts and then return only those that have the ranking of 1:

SELECT castID,
       Cnt
FROM   (
          SELECT   castID,
                   COUNT(*) AS Cnt,
                   @r := IFNULL(@r, 0) + 1 AS r
          FROM     atable
          GROUP BY castID
          ORDER BY Cnt DESC
       ) AS s
WHERE r = 1
;

Please note that with the above method the variable must either not exist or be pre-initialised with a 0 or NULL prior to running the query. To be on the safe side, you could initialise the variable directly in your query:

SELECT s.castID,
       s.Cnt
FROM   (SELECT @r := 0) AS x
CROSS JOIN
       (
          SELECT   castID,
                   COUNT(*) AS Cnt,
                   @r := @r + 1 AS r
          FROM     atable
          GROUP BY castID
          ORDER BY Cnt DESC
       ) AS s
WHERE s.r = 1
;

How to get the maximum count, and ID from the table SQL

3 Answers3