The changes were made in VFP8. See
INFO: SQL SELECT Behavior Changes in Visual FoxPro 8.0 mskb #
813361.
You've many ways to fix your select
Include all non-aggregate fields into GROUP BY
enclose all non-aggregate fields that are not in GROUP BY into aggregate functions MIN()/MAX()
remove all non-aggregate fields that are not in GROUP BY from the column list.
You can use a combination of 3.
If you're pressed for time, you can use SET ENGINEBEHAVIOR 70 as temporary measure. Keep in mind that it doesn't fix the problem but simply hides it. It also will change other SQL behaviors as documented in VFP9 help under SET ENGINEBEHAVIOR
>I just upgraded to VFP 9.0, where the syntax for the GROUP BY clause has changed and requires to list all the fields. I am new to sql-queries.
> I want to select multiple fields from a table but SUM & GROUP BY by field (itm_nuymb) only. Can someone give me a proper syntax to accomplish that. Listed below is a sample code which I think need a sub-query and I am new to that.
>
>> SELECT itm_numb, itm_desc, sls_code, tax_code, SUM(unt_qtty) AS nQty ;
> FROM table1 GROUP BY itm_numb ORDER BY itm_numb
>
>
--sb--
