sql query against redshift data

user_9845366 0
Viewed 46

I have the following table in redshift:

ID, Category
flinch, cat
flinch, cat
mara, dog
mara, cat

the aim here is to get for each ID the number of occurences of each category , hence the expected resut would be:

ID,Category,size
flinch, cat, 2
mara, dog, 1
mara, cat, 1

I tried several queries but got exceptions :ERROR: could not identify an ordering operator for type record Hint: Use an explicit ordering operator or modify the query.

amazon-redshift sql
2 Answers

You seem to want group by:

select id, category, count(*) as size
from t
group by id, category;
user_11440350

Did you try this one?

select id, category, count(*)
from mytable
group by id, category
user_134735250
Related