CODEWITHSUNDEEP
sqldatabaseoracle-databasecount
Zoey Malkov
Zoey Malkov

Reputation: 832

How to do a count 2 columns

I have the following table

cus_id gov_id name
1 aa Bob
1 bb Bill
1 aa James
2 cc Sam
3 aa Sarah
1 aa Joe
2 cc Nathan

As you can see when the cus_id=1 and gov_id=aa there are 3 duplicates, thus the count is 3. I want to count how many instances where the cus_id and gov_id are the same, as in the row.

When cus_id=2 and gov_id=cc there are 2 duplicates. I want the output like this:

cus_id gov_id name count
1 aa Bob 3
1 bb Bill 1
1 aa James 3
2 cc Sam 2
3 aa Sarah 1
1 aa Joe 3
2 cc Nathan 2

I tried:

SELECT cus_id, gov_id, name, count(*) as count
FROM test_table;

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use analytics functions:

select t.*,
       count(*) over (partition by cus_id, gov_id) as cnt
from t;

Upvotes: 2

Related Questions