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.