Skip to content
Advertisement

count(distinct) over (partition by… doesn’t work in Oracle SQL

enter image description here

I want to count the distinct day_number over the past 30 days. However, the distinct function can’t be used with over

If I delete distinct, it will give me the total count of the day_number, but day_number can have lots of duplicate. So that’s why I want to add distinct.

Can anyone please show me how to count the distinct numbers in the over(partition by..) statement? Thanks in advance.

Advertisement

Answer

You can do this by first creating a column that only lists each id once, and then doing a range-count on that column, e.g.:


ETA: proof that the above technique works for your data:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement