Non-group-by expression error

[ShadowMage]

Hey guys, I'm getting an error in my SQL statement. This is the error:Warning: odbc_exec() [function.odbc-exec]: SQL error: [DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Non-group-by expression in select clause (7641), SQL state S1000 in SQLExecDirect in c:\Inetpub\phplib\ODBC.php on line 229Error! Couldn't Run Query:SELECT OrderHed.OrderNum, CustCnt.CustNum, CustCnt.ConNum, CustCnt.Name, CustCnt.Address1, CustCnt.Address2, CustCnt.Address3, CustCnt.City, CustCnt.State, CustCnt.Zip, CustCnt.PhoneNum, CustCnt.FaxNum FROM OrderHed OrderHed INNER JOIN CustCnt CustCnt ON OrderHed.Company = CustCnt.Company AND OrderHed.CustNum = CustCnt.CustNum WHERE ( ( OrderHed.OpenOrder <> 0 AND OrderHed.VoidOrder = 0 ) ) GROUP BY OrderHed.OrderNum, CustCnt.CustNum, CustCnt.ConNum, CustCnt.NameError Message: [DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Non-group-by expression in select clause (7641)What does 'Non-group-by expression in select clause' mean? I don't see anything wrong with the query.

[justsomeguy]

When you use group by you typically use aggregate functions like AVERAGE, SUM, COUNT, etc. It doesn't really make sense to just select a bunch of columns and group by another one, you're not telling it which data to group and how to group it, only which column to group by. This query, for example, would return the total sales for all products:SELECT SUM(price), product FROM table GROUP BY productThat tells it which data to group (the price), how to group it (sum it), and what to group by (product). The reason product doesn't need an aggregate function in the select list is because that's the field it's grouping by.

[ShadowMage]

Hmmm....Okay so why does this query work just fine, then:SELECT OrderHed.OrderNum, CustCnt.CustNum, CustCnt.ConNum, CustCnt.Name, CustCnt.SpecialAddress FROM OrderHed OrderHed INNER JOIN CustCnt CustCnt ON OrderHed.Company = CustCnt.Company AND OrderHed.CustNum = CustCnt.CustNum WHERE ( ( OrderHed.OpenOrder <> 0 AND OrderHed.VoidOrder = 0 ) ) GROUP BY OrderHed.OrderNum, CustCnt.CustNum, CustCnt.ConNum, CustCnt.Name, CustCnt.SpecialAddressAll I did was remove the SpecialAddress field and add a bunch of others.

[justsomeguy]

That works because all of the fields in the select list are also in the group by clause. It breaks when you add a field to the select list which is both not in the group by clause and also not part of an aggregate function.

[ShadowMage]

I see. Thanks, jsg!BTW, I got the query working by just removing the group by clause.