Lucas,

Thank you.

Sorry for my terrible english.

You are right, it meets my requirement approximately.

for losing some info in my question, the approach doesn't reach my expectation fully. The result will be like:

a 40
a1 20
a2 5
a3 3
a12 12

b 17
b2 11
b21 1
b3 5

aa 5
aa3 1
aa5 4

That is, the group value (ie. a, b, aa) should be listed with the details. It can be accomplished through 'union'.


Thanks.

--- In firebird-support@yahoogroups.com, Lucas Franzen <luc@...> wrote:
>
>
>
> 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.
>