Using row_number (or alternative) to count by group, and also only distinct per group?

Question

I am trying to count distinct values per account id using row_number()

This is an example of data that I have:


ID | val
_____________
1  | a
1  | a
1  | b
2  | a
3  | c
3  | a
3  | b
4  | d
4  | d
5  | a

I want to basically count unique values per unique ID. I have tried to use row_number() partition over.

This is an example of the output I want:

ID | val | rank | count
_____________
1  | a  | 1  | 2
1  | b  | 2  | 3
2  | a  | 1  | 1
3  | c  | 1  | 1
3  | a  | 2  | 1
3  | b  | 3  | 3
4  | d  | 1  | 2
4  | e  | 2  | 2
5  | a  | 1  | 1

I've tried this:

%sql
-- Show 
select * from (
select `ID`,`val`, dense_rank() over (partition by `ID` order by `val` asc) as row_num
from table1
)
order by `ID` asc

Which resets the counts based off of new ID, but doesn't count distinct val's. AKA gives me this:

ID | val | rank
_____________
1  | a  | 1
1  | a  | 2
1  | b  | 3
2  | a  | 1
3  | c  | 1
3  | a  | 2
3  | b  | 3
3  | b  | 4
3  | b  | 5
4  | d  | 1
4  | d  | 2
4  | e  | 3
4  | e  | 4
5  | a  | 1

Possible duplicate of [SQL Server query - Selecting COUNT(\*) with DISTINCT](https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) — Clockwork-Muse, Aug 27 '19 at 21:00
Check out `DENSE_RANK()` and try `DENSE_RANK() OVER (ORDER BY ID, val)` [This link](https://codingsight.com/methods-to-rank-rows-in-sql-server-rownumber-rank-denserank-and-ntile/) may help. — dvo, Aug 27 '19 at 21:03

score 0 · Answer 1 · answered Aug 27 '19 at 21:11

You can use dense_rank() to order them . . .

select id, val, dense_rank() over (partition by id order by val) as seqnum
from t;

However, the resulting rank is going to be alphabetical. Your sample suggests that you want to keep the original ordering. If so, first you need a column that expresses the ordering. Then, you can use two levels of aggregation:

select id, val, dense_rank() over (partition by id order by min_oc)
from (select t.*, min(<ordering column>) over (partition by id, val) as min_oc
      from t
     ) t

Using row_number (or alternative) to count by group, and also only distinct per group?

1 Answers1