An expression cannot be used in the GROUP BY, unless it evaluates to an integer representing a field # in the query field list
SELECT substring(ID,1,5) AS quant , SUM(QUANT) as xQuant FROM TEST GROUP BY quant
>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.
--sb--