Problem while trying to aggregate in subquery

Moderator: NorbertKrupa

Post Reply
sameer
Newbie
Newbie
Posts: 2
Joined: Thu Jan 31, 2013 5:32 am

Problem while trying to aggregate in subquery

Post by sameer » Fri Jan 24, 2014 10:51 am

This is a exact sample of a query which I have been trying to run...

Code: Select all

SELECT  'abc'                                  AS ctx,
             SUM(A.TID)                       AS TID,
             SUM(A.AMT)                      AS AMT,
             SUM(A.BMT)                      AS BMT,
            (SELECT SUM(rzrm)
             FROM   abcd o)                  AS rzrm
FROM   xyz a
GROUP  BY 1  
This version works well in oracle but when trying it in vertica I get following error
[Vertica][VJDBC](4818) ERROR: Subqueries in the SELECT or ORDER BY are not supported if the subquery is not part of the GROUP BY
Any suggestions...

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

Re: Problem while trying to aggregate in subquery

Post by JimKnicely » Fri Jan 24, 2014 4:41 pm

Maybe something like this?

Code: Select all

dbadmin=> select * from xyz;
 tid | amt | bmt
-----+-----+-----
   1 |   1 |   1
   1 |   1 |   1
(2 rows)

dbadmin=> select * from abcd;
 rzrm
------
    2
    2
(2 rows)

Code: Select all

dbadmin=> SELECT ctx,
dbadmin->        tid,
dbadmin->        amt,
dbadmin->        bmt,
dbadmin->        (SELECT SUM(rzrm)
dbadmin(>           FROM abcd o) AS rzrm
dbadmin->   FROM (SELECT 'abc'      AS ctx,
dbadmin(>                SUM(A.TID) AS TID,
dbadmin(>                SUM(A.AMT) AS AMT,
dbadmin(>                SUM(A.BMT) AS BMT
dbadmin(>           FROM xyz a
dbadmin(>          GROUP BY 1) foo;
 ctx | tid | amt | bmt | rzrm
-----+-----+-----+-----+------
 abc |   2 |   2 |   2 |    4
(1 row)
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 SQL”