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

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

sqlsqlite


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

SELECT COUNT (*) FROM (SELECT * FROM USERS U INNER JOIN SEARCHES S ON S.USER_ID = U.USER_ID ) d

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 jkoch
Solution 3

Related Questions