CODEWITHSUNDEEP
mysql
skolind
skolind

Reputation: 1754

MySQL - Count on a join

I'm trying to count freelanceFeedback's and order by the count like this:

$sql = "SELECT authentication.*, (SELECT COUNT(*) FROM freelanceFeedback) as taskscount FROM authentication
            LEFT JOIN freelanceFeedback
            ON authentication.userId=freelanceFeedback.FK_freelanceWinnerUserId
            WHERE `FK_freelanceProvider`=$what
            ORDER BY taskscount DESC";

But I'm having multiple outputs if the user has multiple feedbacks and it's not ordering by the taskscount.

I can't figure out what the 'tweet' is wrong..

** UPDATE ** I think I've got it myself: 

$sql = "SELECT DISTINCT authentication.*, 
            (SELECT COUNT(*) FROM freelanceFeedback
            WHERE FK_freelanceWinnerUserId=userId
            ) as taskscount 
            FROM authentication
            WHERE `FK_freelanceProvider`=$what
            ORDER BY taskscount DESC";

This is only outputting 1 user and ORDERING by the amount of feedbacks.

Upvotes: 0

Views: 95

Answers (1)

Matt Gibson
Matt Gibson

Reputation: 14949

When you use COUNT(), you also need to use GROUP BY:

    SELECT authentication.userId, 
           COUNT(freelanceFeedback.id) AS taskscount 
      FROM authentication
 LEFT JOIN freelanceFeedback
        ON authentication.userId = freelanceFeedback.FK_freelanceWinnerUserId
     WHERE `FK_freelanceProvider`= $what
  GROUP BY authentication.userId
  ORDER BY taskscount DESC

However, this will only work if you are not doing SELECT * (which is bad practice anyway). Everything that's not in the COUNT bit needs to go into GROUP BY. If this includes text fields, you'll not be able to do it, so you'll need to do a JOIN to a subquery. MySQL won't complain if you don't but it can seriously slow things down and other DBs will throw an error, so best to do it right:

    SELECT authentication.userId, 
           authentication.textfield, 
           authentication.othertextfield,
           subquery.taskscount
      FROM authentication
 LEFT JOIN (SELECT freelanceFeedback.FK_freelanceWinnerUserId,
                   COUNT(freelanceFeedback.FK_freelanceWinnerUserId) AS taskscount 
              FROM freelanceFeedback
          GROUP BY FK_freelanceWinnerUserId) AS subquery
        ON authentication.userId = subquery.FK_freelanceWinnerUserId
     WHERE authentication.FK_freelanceProvider = $what
  ORDER BY subquery.taskscount DESC

It's not clear what table the FK_freelanceProvider is part of so I've assumed it's authentication.

Upvotes: 1

Related Questions