mySQL - count the number of instances with the same value, for a different user

Question

I have a table, ReadingList, which consists of a few columns. For the sake of simplicity, lets call them:

reading_id

link_id_fk

user_id_fk

reading_id

is just an AI key.

link_id_fk

can exist multiple times (for each

user_id_fk

)

What I'm trying to do, is find the top 10

link_id_fk

values, that are stored by the most users.

Here is my rubbish attempt at it (I just get 145 returned, which I'm not sure where its getting that number - but its certainly not the number I was expecting :))

SELECT

  *,

  (SELECT COUNT(*)

   FROM ReadingList

   WHERE link_id_fk = ReadingList.link_id_fk) AS total_saves

FROM

  ReadingList

WHERE

  user_id_fk IN ('test123', 'test2') AND

  link_id_fk > 0 AND deleted = 0;

an desired example output would be:

link_id_fk   total_saves 

12            3 

34            2 

56            1

78            1

Could anyone point me in the right direction?

Thanks!

Answer 1

you can use a GROUP BY in a subquery:

SELECT rl2.link_id_fk, rl2.nb_link
FROM (
  SELECT link_id_fk, count(*) as nb_link
  FROM ReadingList
  WHERE
    user_id_fk IN ('test123','test2')
    AND link_id_fk > 0 
    AND deleted = 0
  GROUP BY link_id_fk
) as rl2
ORDER BY rl2.nb_link DESC
LIMIT 10;

mySQL - count the number of instances with the same value, for a different user

Sign up