CODEWITHSUNDEEP
mysql
Philip
Philip

Reputation: 393

MySql Query: Getting a record count within query

I got my query working but it doesn't count the rows... in my left outer join.

 SELECT mUserId,mUserName,COALESCE(x.likeId,0) AS likeCount 
    FROM likes 
    LEFT JOIN members ON likes.likeMember = members.mUserId 
    LEFT OUTER JOIN (SELECT likeId, count(*) n FROM likes WHERE likeMember = likes.likeMember) x ON likes.likeMember = x.likeId 
    WHERE likeDate > '2014-11-16 07:44:47' 
    GROUP BY likeMember 
    ORDER BY `likeCount` DESC

Any suggestions?

Upvotes: 1

Views: 9

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

This is your query:

SELECT mUserId,mUserName,COALESCE(x.likeId,0) AS likeCount 
FROM likes LEFT JOIN
     members
     ON likes.likeMember = members.mUserId LEFT OUTER JOIN
     (SELECT likeId, count(*) n
      FROM likes
      WHERE likeMember = likes.likeMember
     ) x
     ON likes.likeMember = x.likeId 
WHERE likeDate > '2014-11-16 07:44:47' 
GROUP BY likeMember 
ORDER BY `likeCount` DESC;

It is a bit absurd. Either do an aggregation in the subquery. Or do an aggregation in the outer query. But not both. I suspect you want something more like this:

SELECT m.mUserId, m.mUserName, COUNT(*) AS likeCount 
FROM likes l LEFT JOIN
     members m
     ON l.likeMember = m.likeId 
WHERE l.likeDate > '2014-11-16 07:44:47' 
GROUP BY l.likeMember 
ORDER BY `likeCount` DESC;

The problem with your subquery is the WHERE clause. You think it is correlated to the outer query. But it is really interpreted as:

      WHERE likes.likeMember = likes.likeMember

In other words, the condition is true whenever likes.likeMember is not NULL.

Upvotes: 2

Related Questions