CODEWITHSUNDEEP
sqloracle-database
lisam
lisam

Reputation: 59

Counting the number of unique values by group

Please tell me how you can count the number of unique "inn" values for each group where "publid", "clusterid", "issuedate", "operdate" are the same, as shown in the example.

Example

|*inn*|*publid*|*clusterid*|*issuedate*|*operdate*|
|-----|--------|-----------|-----------|----------|
| 333 |   1    |    12     |  01-01-21 | 05-01-21 |
| 222 |   1    |    12     |  01-01-21 | 05-01-21 |
| 333 |   2    |    12     |  01-01-21 | 05-01-21 |
| 222 |   2    |    12     |  01-01-21 | 05-01-21 |
| 111 |   2    |    12     |  01-01-21 | 05-01-21 |
|-----|--------|-----------|-----------|----------|

Result

|*inn*|*publid*|*clusterid*|*issuedate*|*operdate*|*count*|
|-----|--------|-----------|-----------|----------|-------|
| 333 |   1    |    12     |  01-01-21 | 05-01-21 |   2   |
| 222 |   1    |    12     |  01-01-21 | 05-01-21 |   2   |
| 333 |   2    |    12     |  01-01-21 | 05-01-21 |   3   |
| 222 |   2    |    12     |  01-01-21 | 05-01-21 |   3   |
| 111 |   2    |    12     |  01-01-21 | 05-01-21 |   3   |
|-----|--------|-----------|-----------|----------|-------|

Upvotes: 0

Views: 408

Answers (2)

George Joseph
George Joseph

Reputation: 5922

Since you are looking for unique values i would do as follows

SELECT * 
       ,COUNT(distinct "inn") OVER (PARTITION BY "publid", "clusterid", "issuedate", "operdate") AS "count" 
  FROM TABLE

Upvotes: 1

VvdL
VvdL

Reputation: 3210

You can use a window function:

SELECT *, 
COUNT("inn") OVER (PARTITION BY "publid", "clusterid", "issuedate", "operdate") AS "count" 
FROM TABLE

Upvotes: 0

Related Questions