Am 01.12.2011 10:23, schrieb ibmcom2011:

> Suppose a table like below:
>
> CREATE TABLE T1(
> F1 varchar(20),
> V1 integer
> );
>
> Now, it has some values in like these:
>
> insert into t1(f1, v1) values(a1, 20);
> insert into t1(f1, v1) values(a2, 5);
> insert into t1(f1, v1) values(a3, 3);
> insert into t1(f1, v1) values(a12, 12);
>
> insert into t1(f1, v1) values(b2, 11);
> insert into t1(f1, v1) values(b21, 1);
> insert into t1(f1, v1) values(b3, 5);
>
> I hope the result is:
> All start as 'a' will be totoled into a new record 'a', and all start as 'b' will be totaled into a new record 'b'.
>
> Certainly, the 'a' and 'b' is only a sample to describe, in fact, I don't know what the client would give me .

I'm not sure I do understand you right and you want sth. back like.

a 40
b 17

If so, you can use:

SELECT SUBSTRING (F1 FROM 1 FOR 1), SUM ( V1)
FROM T1
GROUP BY 1

(by the way:
insert into t1(f1, v1) values(a1, 20);

has to look like:
insert into t1(f1, v1) values('a1', 20);


hth
luc.