I'd like to get a count of a column's distinct values using the analytic function COUNT, but it doesn't work in Vertica:
Code: Select all
dbadmin=> select a, b, count(b) over (partition by a) from test;
a | b | ?column?
---+---+----------
1 | 1 | 3
1 | 1 | 3
1 | 2 | 3
(3 rows)
dbadmin=> select a, b, count(distinct b) over (partition by a) from test;
ERROR 4249: Only MIN/MAX are allowed to use DISTINCT
Anyway, I want this result:
Code: Select all
a | b | ?column?
---+---+----------
1 | 1 | 2
1 | 1 | 2
1 | 2 | 1
Can anyone help with a work around?