Advertisements
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;