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
link_id_fk
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;
link_id_fk total_saves
12 3
34 2
56 1
78 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;