>Here's my current sql statement:
>SELECT ID, desc, SUM(QUANT) as xQuant FROM TEST GROUP BY ID
>
>The ID has variants, AB001HB, AB001SB, etc. Right now the statement shows the sum of the quant for each variant. I want to show the sum of the quantity (quant) of the substring(ID,1,5), thus combining the sums for the variants.
>
>SELECT substring(ID,1,5), desc, SUM(QUANT) as xQuant FROM TEST GROUP BY substring(ID,1,5) gives an error message stating that this is an illegal group by clause.
>
> What changes should I make to the sql statement to make this work?
>
>Thanks in advance.

Rixon,
In VFP group by you need to use either ordinal of expression, or name (but not the expression itself like as it can be on other backends):

SELECT substring(ID,1,5), desc, SUM(QUANT) as xQuant FROM TEST GROUP BY 1,2

is the easiest. You can also name the columns but "desc" is a reserved word meaning descending so use 1,2.

Cetin