Query Help - Column must appear in the GROUP BY clause

I need some help with analytic functions as I cannot figure out why the below is wrong.

SELECT
a.HoldingCompanyID,
a.HoldingCompanyName,
a.Metric,
a.Frequency,
SUM(a.Spend) AS SpendTotal,
SUM(a.Spend) OVER (PARTITION BY a.Metric, a.Frequency)
FROM temp.rt_test a
GROUP BY 1,2,3,4;

[Code: 2640, SQL State: 42803] [Vertica]VJDBC ERROR: Column "a.Spend" must appear in the GROUP BY clause or be used in an aggregate function

If I take out " SUM(a.Spend) AS SpendTotal” and remove the GROUP BY then it works. Looking at documentation I believe GROUP BY is not used in analytic functions? But when I put back " SUM(a.Spend) AS SpendTotal” then I get the same error.
a.HoldingCompanyID,
a.HoldingCompanyName,
a.Metric,
a.Frequency,
SUM(a.Spend) AS SpendTotal,
SUM(a.Spend) OVER (PARTITION BY a.Metric, a.Frequency)
FROM temp.rt_test a

[Code: 2640, SQL State: 42803] [Vertica]VJDBC ERROR: Column "a.HoldingCompanyID" must appear in the GROUP BY clause or be used in an aggregate function

This works…
SELECT
a.HoldingCompanyID,
a.HoldingCompanyName,
a.Metric,
a.Frequency,
SUM(a.Spend) OVER (PARTITION BY a.Metric, a.Frequency)
FROM temp.rt_test a

Best Answer

Answers

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    Hi,

    I am not exactly sure what you are trying to accomplish, but all columns NOT listed in the GROUP BY clause need to have an aggregate function.

    Example:

    dbadmin=> SELECT * FROM temp.rt_test ORDER BY 1, 2, 3, 4;
     HoldingCompanyID | HoldingCompanyName | Metric | Frequency | Spend
    ------------------+--------------------+--------+-----------+-------
                    1 | A                  |      1 |         1 |     1
                    1 | A                  |      1 |         1 |     2
                    1 | A                  |      2 |         1 |     1
                    1 | A                  |      3 |         1 |     1
                    2 | B                  |      1 |         1 |     1
    (5 rows)
    
    dbadmin=> SELECT
    dbadmin-> a.HoldingCompanyID,
    dbadmin-> a.HoldingCompanyName,
    dbadmin-> a.Metric,
    dbadmin-> a.Frequency,
    dbadmin-> SUM(a.Spend) AS SpendTotal,
    dbadmin-> SUM(SUM(a.Spend)) OVER (PARTITION BY a.Metric, a.Frequency)
    dbadmin-> FROM temp.rt_test a
    dbadmin-> GROUP BY 1,2,3,4
    dbadmin-> ORDER BY 1,2,3,4;
     HoldingCompanyID | HoldingCompanyName | Metric | Frequency | SpendTotal | ?column?
    ------------------+--------------------+--------+-----------+------------+----------
                    1 | A                  |      1 |         1 |          3 |        4
                    1 | A                  |      2 |         1 |          1 |        1
                    1 | A                  |      3 |         1 |          1 |        1
                    2 | B                  |      1 |         1 |          1 |        4
    (4 rows)
    
    dbadmin=> SELECT
    dbadmin-> a.HoldingCompanyID,
    dbadmin-> a.HoldingCompanyName,
    dbadmin-> a.Metric,
    dbadmin-> a.Frequency,
    dbadmin-> SUM(a.Spend) AS SpendTotal,
    dbadmin-> MAX(SUM(a.Spend)) OVER (PARTITION BY a.Metric, a.Frequency)
    dbadmin-> FROM temp.rt_test a
    dbadmin-> GROUP BY 1,2,3,4
    dbadmin-> ORDER BY 1,2,3,4;
     HoldingCompanyID | HoldingCompanyName | Metric | Frequency | SpendTotal | ?column?
    ------------------+--------------------+--------+-----------+------------+----------
                    1 | A                  |      1 |         1 |          3 |        3
                    1 | A                  |      2 |         1 |          1 |        1
                    1 | A                  |      3 |         1 |          1 |        1
                    2 | B                  |      1 |         1 |          1 |        3
    (4 rows)
    

Leave a Comment

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