Search code examples
sqlgroup-bysnowflake-cloud-data-platformpivot-table

How to find count of groups a particular IDs belong to in Snowflake?

I have a following data that a 5 groups which has certain IDs. One ID can be a part of multiple groups. I would like to find in how many group each ID is in.

Initial Data

Result: Result

I did this excel but I want do it in snowflake

Any help/suggestion would be appreciated.

Thank you!

I tried to use Select ID, COUNT(Group) FROM _Database

and expecting Result

but it says not a valid group by expression.

Solution

  • You should add the group by clause in your query , if it still gives some error, please send us the table sample, with some sample data and expecte output.

    Select ID, COUNT(Group) FROM _Database GROUP BY ID;