Search code examples
mysqlsqlmysql-error-1111

MySQL, Confused w/ query / Error Code: 1111. Invalid use of group function.

I have 3 tables. 

  Video (Id, ViewCount, Location)
  Likes (Id, Video, User)
  Location (Id, Name)

How would I query the four highest ranked videos (sum of Video.ViewCount + count(Likes.User) Group them by Video.Id, and return the top 4 results from a specific location.

I've tried this: 

SELECT  Video.Id, sum(Video.ViewCount + count(Likes.User)) as Points From Video
  Left Join Likes
  ON Likes.Video=Video.Id
  WHERE Video.Location=30
  GROUP BY Video.Id
  ORDER BY Points DESC
  LIMIT 4;

But I get an invalid use of group function. Does anybody have any pointers ??

Solution 
  • SELECT  id,
        viewcount +
        (
        SELECT  COUNT(*)
        FROM    likes l
        WHERE   l.video = v.id
        ) AS points
FROM    video v
WHERE   location = 30
ORDER BY
        points DESC, id
LIMIT 4