Mohamed  Kira Mohamed Kira - 5 months ago 33
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,
FROM movies
LEFT JOIN lists ON ( = lists.movie_id)
group by lists.movie_id, movies.genre1,

this sql query returning


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 ?


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 = lists.movie_id
    group by lists.user_id, movies.genre1