The Vertica Database Forums

Posted: **Thu Aug 23, 2012 7:31 pm**

Hello,

Is there a way around the error "ERROR 2792: Correlated subquery with aggregate function COUNT is not supported".

Simple example:

Code: Select all

vert01=> create table test (c1 int, c2 int);
CREATE TABLE

dbadmin=> select c1, c2, (select count(c2) from test t2 where t2.c1 = t1.c1 and t2.c2 = 0) c2_sum from test t1;
ERROR 2792:  Correlated subquery with aggregate function COUNT is not supported

I have a much larger query where I need to get a count like this...

Thanks in advance for any suggestions ...

Posted: **Thu Aug 23, 2012 8:17 pm**

You can try re-writing the COUNT using a SUM...

Maybe something like this?

Code: Select all

select c1, c2, (select sum(case
                             when t2.c2 = 0 then 1
                             else 0
                           end)
                  from test t2
                 where t2.c1 = t1.c1) c2_sum
  from test t1;

The Vertica Database Forums

ERROR 2792

ERROR 2792

Re: ERROR 2792