How to select count using sub-select in sql?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

This can be done done with the GROUP BY clause, COUNT and a LEFT JOIN. No need to use a sub-select.

SELECT users.username, users.created_at, users.email, users.phone, COUNT(searches.user_id) AS total
FROM users
LEFT JOIN searches on users.id = searches.user_id
GROUP BY users.id, users.username, users.created_at, users.email, users.phone