CODEWITHSUNDEEP
sqlcountsnowflake-cloud-data-platform
JCG
JCG

Reputation: 39

Get a distinct row count from another column

SQL Table is as follows:

Category | Subcategory |  
A          1
A          1
A          2
B          1
B          2

I need the number of each subcategory for each category, not including duplicate subcategories within the category.

You'll notice there are 3 total "1" subcategories, but only a count of 2 as the duplicate is redundant and not included.

Example output:

subcategory | count
1             2
2             2

How can I achieve this? I am familiar with COUNT but I can only get the raw number of rows.

Using Snowflake.

Thanks!

Upvotes: 1

Views: 85

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You can use GROUP BY, as in:

select Category, count(distinct Subcategory)
from t
group by Category

Upvotes: 3

Related Questions