How to select count using sub-select in sql?

March 14, 2022

I’m trying to get a count of an inner join. Should I use a sub-select?

const users = db.queryEntries(
  "SELECT username, created_at, email, phone FROM users WHERE contactme = 1 ORDER BY created_at DESC"
);

There’s another table called searches that has a user_id attribute. I want to get the number of searches for each user returned in the first query.

Would be something like:

SELECT count(*) as total
FROM searches
WHERE searches.user_id = user.id

…such that the first query will return total for each user

>Solution :

if I understand correctly, you can try to use COUNT with GROUP BY in subquery, then do JOIN by userid

SELECT u.username, u.created_at, u.email, u.phone ,s.total
FROM users u
INNER JOIN (
    SELECT count(*) as total,user_id
    FROM searches 
    GROUP BY user_id
) s ON s.user_id = u.id
WHERE u.contactme = 1 
ORDER BY u.created_at DESC