ERROR 2792

Moderator: NorbertKrupa

Post Reply
User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

ERROR 2792

Post by Jbaskin » 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 ...

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR 2792

Post by JimKnicely » 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;
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Error Codes”