SQL: Count the Number of Colors Between a Range of Dates for Each ID

I am working with Netezza SQL.

I have the following table (my_table):

  id    my_date color
1  1 2009-01-01   red
2  1 2010-05-05  blue
3  1 2011-01-01   red
4  1 2012-01-01   red
5  2 2009-01-01  blue
6  2 2009-05-05   red
7  2 2010-01-01 green
8  2 2010-05-05   red
9  2 2020-01-01   red

My Question: For each ID – I want to find out between 2010-01-01 and 2015-01-01, what was the number of times each color appeared?

The final answer should look like this:

  id color count
1  1   red     2
2  1  blue     1
3  2   red     2
4  2 green     1

Here is what I tried so far (select rows that match the date condition and then do a group by count on id and color):

SELECT id, color, COUNT(*) as count
FROM my_table
WHERE my_date BETWEEN '2010-01-01' AND '2015-01-01'
GROUP BY id, color
ORDER BY id, color;

  id color count
1  1  blue     1
2  1   red     2
3  2 green     1
4  2   red     1

The output is not matching the desired result – can someone please show me what I am doing wrong and what I can do to fix this?

Thanks!

Note: Here is the data for this problem:

(
id = c(1,1,1,1,2,2,2,2,2), 
my_date = c("2009-01-01", "2010-05-05", "2011-01-01", "2012-01-01", "2009-01-01", "2009-05-05", "2010-01-01", "2010-05-05", "2020-01-01"),
color = c("red", "blue", "red", "red", "blue", "red", "green", "red", "red")
)

>Solution :

It looks like your only problem is in the ORDER BY clause:

SELECT id, color, COUNT(*) AS count
FROM my_table
WHERE my_date BETWEEN '2010-01-01' AND '2015-01-01'
GROUP BY id, color
ORDER BY id, COUNT(*) DESC;