Adding a constant value column in the group by clause

Question

Netezza sql is giving error on this query:Cause: Invalid column name 'dummy'.

 select col1,col2, '' as dummy, max(col3) from table1  group by col1,col2,dummy

If i remove the dummy from the group by clause, it works fine. But as per sql syntax, I am supposed to include all non aggregate columns in group by.

Bayreuth

"Non Aggregate columns".. not values.. you already answered your question! – Flyaway

Answer 1

why do you need it in your group by, you can use an aggregate function and its result would always be right because the value is constant for example:

select col1,col2, min(' ') as dummy, max(col3) from table1  group by col1,col2

Pudding

Answer 2

"dummy" is a static column (not in the table), so it does not need to be in the group by because it is an external column.

SELECT col1,
       col2,
       cast(5 as int) AS [dummy],
       max(col3)
FROM test_1
GROUP BY col1,
         col2,
         col3,
        'dummy'

The code produces an outer reference error # 164.

Take a look at these links
http://www.sql-server-helper.com/error-messages/msg-164.aspx

http://www.sql-server-helper.com/error-messages/msg-1-500.aspx

Kathrinkathrine

Answer 3

It's due to the order of operations...

FROM JOIN WHERE GROUP BY ... SELECT

When using group by, only the fields remaining from the previous step are available. Since you are not declaring your "Dummy" column until the Select statement the group by doesn't know it exists and therefore doesn't need to account for it.

Claiborn

Answer 4

Going by Basics..GROUP BY operations are something executed ,after the JOIN operations underneath(File IOs).. And then only the SELECTED resultet would be available.

Now that, you specified something as Dummy in SELECT, and the database would not know it, Because While GROUPing it is not available at the TABLE level.!

Try your query using GROUP BY your_column, ' ' it would work.. Because you have mentioned it directly instead referring an alias!

Finally, when a GROUP by is used.. You can specify any constants in SELECT or GROUP BY.. because they are afterall included in your SELECTed result, without a TABLE operation involved. So the database excuses them.

Flyaway

Answer 5

To resolve the issue, group it at an outer layer:

SELSE X.col1, X.col2, X.dummy, max(col3)
FROM (
  SELECT col1,
         col2,
         cast(5 as int) AS [dummy],
         col3
  FROM test_1
)
GROUP BY X.col1,
         X.col2,
         X.dummy

Possum