Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to aggregate values in a sub query??

How to aggregate values in a subquery. I have tried following query
SELECT    'abc'                             AS ctx,
                  'ALL'                             AS Anpe,
                  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
 but i get the error as
 Subqueries in the SELECT or ORDER BY are not supported if the subquery is not part of the GROUP BY

Comments



  • Create a query something like, may be you can refine the column you do not want as duplicate.

    dbadmin=> select * from t1;
     c1 | c2
    ----+----
     10 | 30
    (1 row)

    dbadmin=> select * from t2;
     c1
    ----
     50
     10
    (2 rows)


    dbadmin=> select * from (select 'abc', sum(t1.c1)  as x, sum(t1.c2) as y from t1 group by 1) as foo1, (select 'abc', sum(t2.c1) as z from t2 group by 1) as foo2;

     ?column? | x  | y  | ?column? | z 
    ----------+----+----+----------+----
     abc      | 10 | 30 | abc      | 60




Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.