How to make a nested query?

mysql, sql, subquery

Solution

SELECT  u.*,
        (
        SELECT  COUNT(*)
        FROM    users ui
        WHERE   ui.invited_by_id = u.id
        ) AS cnt
FROM    users u

Problem

Have a table users and there is a field invited_by_id showing user id of the person who invited this user. Need to make a MySQL query returning rows with all the fields from users plus a invites_count field showing how many people were invited by each user. Something like this: ``` SELECT User.*, Count.count FROM users AS User, ( SELECT COUNT(*) AS count FROM users WHERE users.invited_by_id=User.id ) AS Count; ``` This one is not working so I need a working one.

Original source