Geek asks and answers

Adding a constant value column to a proposal group

Netezza sql gives an error for this query: Reason: Invalid column name 'dummy'.

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

If I remove a mannequin from a group by clause, it works fine. But according to sql syntax, I have to include all non-aggregate columns in the group.

+3
source share
4 answers

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

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

This is due to the order of operations ...

FROM JOIN WHERE THE GROUP ON ... SELECT

, , . "Dummy" , Select , , .

+4

"dummy" - ( ), , .

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

# 164.


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

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

+2

.. GROUP BY JOIN (File IOs).. SELECTED.

Now you have specified something as Dummyin SELECT, and the database will not know it, because While GROUPing it is not available at the TABLE level.!

Try executing the query with GROUP BY your_column, ' ', this will work. Because you mentioned it directly, instead referring to an alias!

Finally, when a is used GROUP BY. You can specify any constants in SELECT or GROUP BY .., because they will be included in your result SELECTEDwithout using the TABLE operation. Therefore, the database justifies them.

+1
source

More articles:


All Articles