Andrew Newby Andrew Newby - 7 months ago 11
SQL Question

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

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

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;