Count movies with specific score, per actor I have movies and actors tables. Movies have a rating from 0 to 10. Each actor belongs to a group (table groups). Movies and actors are linked through movies_actors table.

For analytics purposes, I need the number of movies with rating between 0 and 1 (the upper bound excluded, named count0), between 1 and 2 (count1), ..., for every actor in a specific group.

Example of expected result:

actor_id | count0 | count1 | count2 | count3 | count4 | count5 | count6 | count7 | count8 | count9 |
---------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
       1 |      0 |      1 |      0 |      0 |      1 |      0 |      1 |      0 |      0 |      0 |
       3 |      1 |      0 |      0 |      0 |      1 |      0 |      0 |      0 |      0 |      0 |


sqlfiddle here

As you can see in the sqlfiddle, I achieved to get count0, count1, ... for a specific actor whose id is hardcoded into the query.

How do I get this information for every actor in a specific group?

Note: I use Postgres 9.4.1 with Ruby on Rails 5.
You can group by actor_id to get the desired results. You might want to join groups in as well if you want to filter by a column in there. (sqlfiddle)

SELECT
    actor_id,
    COUNT(CASE WHEN score &gt;= 0 and score &lt;= 1 THEN 1 END) count0,
    COUNT(CASE WHEN score &gt; 1 and score  &lt;= 2 THEN 1 END) count1,
    COUNT(CASE WHEN score &gt; 2 and score  &lt;= 3 THEN 1 END) count2,
    COUNT(CASE WHEN score &gt; 3 and score  &lt;= 4 THEN 1 END) count3,
    COUNT(CASE WHEN score &gt; 4 and score  &lt;= 5 THEN 1 END) count4,
    COUNT(CASE WHEN score &gt; 5 and score  &lt;= 6 THEN 1 END) count5,
    COUNT(CASE WHEN score &gt; 6 and score  &lt;= 7 THEN 1 END) count6,
    COUNT(CASE WHEN score &gt; 7 and score  &lt;= 8 THEN 1 END) count7,
    COUNT(CASE WHEN score &gt; 8 and score  &lt;= 9 THEN 1 END) count8,
    COUNT(CASE WHEN score &gt; 9 and score  &lt;= 10 THEN 1 END) count9
FROM movies
 INNER JOIN movies_actors ON movies_actors.movie_id = movies.id 
 INNER JOIN actors ON movies_actors.actor_id = actors.id
WHERE actors.group_id = 1
GROUP BY actor_id