count() not counting genres

Question

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 ?

I Mohamed! I hope this help you http://stackoverflow.com/questions/1221559/count-vs-count1?rq=1 Best regards — owernet, Oct 28 '16 at 09:02

score 2 · Accepted Answer · answered Oct 28 '16 at 09:01

2

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

answered Oct 28 '16 at 09:01

connectedsoftware

6,987
3
28
43

score 2 · Answer 2 · answered Oct 28 '16 at 09:01

2

It's doing this because you're grouping by everything. If you group by just "movies.genre1" you'll have: Crime 3 Thriller 1

You mentioned in your question you want a list of each genre per user but I'm not seeing you actually use the User column?

answered Oct 28 '16 at 09:01

Andrew L

6,618
3
26
30

yeah for brevity sake i have omitted the user's part – Mohamed Kira Oct 28 '16 at 10:03

count() not counting genres

2 Answers2