Adding a constant value column in the group by clause

sql group-by netezza
Victor picture Victor · Feb 7, 2014 · Viewed 16.1k times · Source

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.

Answer

Maryam Arshi picture Maryam Arshi · Feb 7, 2014

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

Related questions

Using group by on multiple columns

I understand the point of GROUP BY x. But how does GROUP BY x, y work, and what does it mean?

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * …

Read More