Query Help - Column must appear in the GROUP BY clause

scottpedersoli

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

scottpedersoli

Thanks Jim, appreciate it!

Jim_Knicely

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)