Using DISTINCT with the COUNT Analytic Function

[Julie]

Howdy!

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

The "Only MIN/MAX are allowed to use DISTINCT" message from Vertica is kind of silly. What's the difference between MIN(DISTINCT b) and MIN(b)... nothing, right?

Anyway, I want this result:

Code: Select all

 a | b | ?column? 
---+---+----------
 1 | 1 |        2
 1 | 1 |        2
 1 | 2 |        1

The DISTINCT option is available in some Oracle code I have

Can anyone help with a work around?

[id10t]

Hi!

[DELETED]

[NorbertKrupa]

What's the difference between MIN(DISTINCT b) and MIN(b)... nothing, right?

There is no difference. The only use case I've seen is COUNT(DISTINCT col).

See the examples in this SQL Fiddle.

[Julie]

Thanks, guys!