Toydor Toydor - 9 months ago 31
SQL Question

Query data by year from joined table

I have one table named

colors
and I need to create a query that returns how many unique colors are used each year based on the date in my other table,
programs
.
It looks like this:

colors
+----+----------+
| id | name |
+----+----------+
| 1 | blue |
| 2 | yellow |
+----+----------+

programs
+----+------------+
| id | date |
+----+------------+
| 1 | 2016-01-08 |
| 2 | 2016-02-08 |
| 3 | 2017-02-08 |
+----+------------+

programs_colors
+------------+----------+
| program_id | color_id |
+------------+----------+
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 1 |
| 3 | 1 |
| 3 | 1 |
+------------+----------+


I have tried with this:

SELECT min(date), count(*) FROM (
SELECT min(date) AS date FROM programs_colors INNER JOIN programs ON programs.id = program_id GROUP BY color_id
) AS a GROUP BY year(date)

min(date): count(*):
2016-01-08 2
2017-01-08 0


But the above query groups my colors as a whole, but I need them grouped by each year

Expected result:

min(date): count(*):
2016-01-08 2
2017-01-08 1


I hope my question makes sense

Answer Source
SELECT  min(date), count(distinct color_id) 
FROM    programs_colors 
INNER   JOIN programs 
ON      programs.id = program_id 
GROUP   BY year(date);