Counting the number of unique values by group-CodePudding

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   |
|-----|--------|-----------|-----------|----------|-------|

CodePudding user response：

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

CodePudding user response：

You can use a window function:

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