Mohamed  Kira Mohamed Kira - 1 month ago 10
SQL Question

count() not counting genres

i have this schema

lists TABLE


id | movie_id(fk) | user_id


Movies TABLE


id(pk) | genre1 | genre2


so i want to get the most reacurrence genres in one user's list
, i tried

SELECT lists.movie_id, movies.genre1, count(movies.genre1) as counting, movies.id
FROM movies
LEFT JOIN lists ON (movies.id = lists.movie_id)
group by lists.movie_id, movies.genre1, movies.id


this sql query returning


[
{"movie_id":100,"genre1":"Crime","counting":1,"id":100},{"movie_id":141267,"genre1":"Crime","counting":1,"id":141267},{"movie_id":207932,"genre1":"Crime","counting":1,"id":207932},{"movie_id":238636,"genre1":"Thriller","counting":1,"id":238636}
]


although Crime genre is present 3 times in the array it counted it once at a time, it should be "counting" : 3 for Crime
what did i do wrong ?

CSL CSL
Answer

This should work, you weren't grouping by user_id at all:

SELECT lists.user_id, movies.genre1, count(movies.genre1) as counting
    FROM movies
    LEFT JOIN lists ON movies.id = lists.movie_id
    group by lists.user_id, movies.genre1