MYsql Query Troubleshooting

SELECT contest.contest_id 
FROM db_user
LEFT JOIN 开发者_开发百科participants ON participants.user_id = db_user.db_user
LEFT JOIN contest ON participants.contest_id = contest.contest_id
WHERE contest.user_id=$user
group by participants.contest_id,participants.user_id";

This returns all users who are part of all contest and works well but i need to find the number of rows it returns. For eg above returns 5 rows i want result to be as

Contest_Id       Number of users
1                       3
2                       4

You need to use the COUNT aggregate function along with an appropriate HAVING clause. See the following: HAVING Clause

Try this:

 select contest.contest_id ,Count(*) as 'No of Users'
    FROM db_user
    LEFT JOIN participants ON participants.user_id = db_user.db_user
    LEFT JOIN contest ON participants.contest_id = contest.contest_id
    WHERE contest.user_id=$user
    group by participants.contest_id;

Try:

    SELECT contest.contest_id, 
(select count(*) from db_user 
      inner JOIN participants ON participants.user_id = db_user.user_id and                   
      participants.contest_id = contest.contest_id)
    FROM contest

You might want to expand your SELECT statement to include COUNT(participants.user_id). A user would be double-counted if he entered the same contest twice, though.

Your query is syntactically wrong as you should always select fields that are mentioned in the GROUP BY clause, although MySQL let you specify other fields, you should be aware that most of the time you will get unpredictable results. That being said, maybe this is the query you are looking for:

SELECT participants.contest_id, COUNT(DISTINCT participants.user_id) AS 'Number of users'
FROM db_user
  LEFT JOIN participants ON participants.user_id = db_user.db_user
  LEFT JOIN contest ON participants.contest_id = contest.contest_id
WHERE contest.user_id = $user
GROUP BY participants.contest_id;