SQL COUNT The returned number of items using a specific property.

Question

I have 3 MySQL tables:

• Colour
  • id_color (1, 2, 3, 4)
  • title ("blue", "red", "orange", "blue").
• subject
  • id_theme (1, 2, 3)
  • title ("dark theme", "light theme", "other theme").
• theme_color
  • id_theme
  • id_color

I need to print a list of colors that are used by all themes. In the same set of records. I need to know how many themes each color uses.

The end result should be something like this:

color.id_color | color.title | COUNT(theme_color.id_theme)
==========================================================
1              | blue        | 2
2              | red         | 1
3              | orange      | 3

(cyan = 4 is not associated with any of the themes in theme_color). I tried to use several methods with COUNT (), but I only get one record field with SUM of all topics used.

Answer 1

SELECT color.id_color, color.title, COUNT(theme_color.id_theme)
  FROM color INNER JOIN theme_color ON color.id_color = theme_color.id_color
GROUP BY color.id_color, color.title