CODEWITHSUNDEEP
mysqlsql
user3399784
user3399784

Reputation:

group by but counting just active rows-SQL

I have the following tables:

scenes
------
scene_id
title
video_url
modified_at
uuid


likes
----
scene_id
uuid
active


users
-----
uuid
username

Problem 1: I want to get the number of likes for scenes,scene_id, author in descending order; please note that we need to count just those likes which are active in likes table!

My try:

SELECT s.scene_id,s.video_url,s.title,s.uuid, u.username as author,s.data,count(l.active) as likes

 FROM scenes s
    left join likes l
        on l.scene_id = s.scene_id
    join users u
        on u.uuid = s.uuid
  where s.video_url  IS not NULL
  group by s.scene_id
  order by modified_at DESC;

But this is counting all of the likes, irrespective whether it is active or not! I dont know how I can add that one in my query!

Problem 2:

Also I wonder whether it is possible to add another column in the result set which shows a specific user (the uuid can be passed and used in where clause) has liked each scene in the result set or not? Is it possible to have this result in just one query?

Please let me know if you need more clarification!

Thanks!

Upvotes: 0

Views: 65

Answers (1)

spencer7593
spencer7593

Reputation: 108480

Adding the predicate on the active column is straightforward; it just depends on how that's represented... is that an integer type column, using 0 and/or NULL to indicate that it's "inactive", and a 1 and/or any positive integer to indicate that it's active?

To get "number of likes for scenes,scene_id, author in descending order", you could do something like this:

SELECT COUNT(l.scene_id) AS cnt_likes
     , s.scene_id        AS scene_id
     , a.username        AS author
  FROM scenes s
  LEFT
  JOIN users a
    ON a.uuid = s.uuid
  LEFT
  JOIN likes l 
    ON l.scene_id = s.scene_id 
   AND l.active+0
 GROUP BY s.scene_id
 ORDER BY cnt_likes DESC, s.scene_id DESC, a.username DESC

If the column active is character type, and you are using a value of "yes" to indicate a like is active, you'd need to change the predicate,

Replace:

   AND l.active+0

With:

   AND l.active = 'yes'

etc.

For the second question, identifying the users that have 'active likes' for a scene

(i.e. rows included in the aggregate), that's problematic, because there can be multiple users with a like for a scene... which of those users do we identify to return, just any one of them, all of them?... i just re-read your question more carefully. You want to know if one particular identified user has a like for the scene.

Just test if the uuid on the like row is equal to the value you are looking for, and return a 0 or 1, and then get the MAX() of that.

e.g.

SELECT COUNT(l.scene_id)         AS cnt_likes
     , s.scene_id                AS scene_id
     , a.username                AS author
     , MAX(l.uuid = 'auser')     AS liked_by_user
  FROM scenes s
  LEFT
  JOIN users a
    ON a.uuid = s.uuid
  LEFT
  JOIN likes l
    ON l.scene_id = s.scene_id 
   AND l.active+0
 GROUP BY s.scene_id
 ORDER BY cnt_likes DESC, s.scene_id DESC, a.username DESC

FOLLOWUP

The expression

     MAX(l.uuid = 11 )

can return 1, 0 or NULL. (1 would be equivalent to boolean TRUE, 0 to boolean false, and a NULL would be unknown, or in this case, we'd treat it as also being FALSE.

In MySQL, integers can be treated as booleans, and booleans are handled as integers.

To return strings 'true' or 'false', you could use an IF() function

     IF(MAX(l.uuid = 11 ), 'true', 'false')

Upvotes: 1

Related Questions