Sorin

Use a derived table

SELECT Name, total, RUNSUM(1)+100 FROM
(SELECT tbl2.Name, SUM(tbl1.SumPay) as total
FROM tbl1
LEFT JOIN tbl2 ON ...
GROUP BY tbl2.Name) T1
GROUP BY Name

John

"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote in
message
> Use a derived table
>
> SELECT Name, total, RUNSUM(1)+100 FROM
> (SELECT tbl2.Name, SUM(tbl1.SumPay) as total
> FROM tbl1
> LEFT JOIN tbl2 ON ...
> GROUP BY tbl2.Name) T1
> GROUP BY Name

Yep this did it.

Thanks
Sorin

Sorin

>Thanks but no.
>
>The counter jump by the number of records inside the group
>not by 1.

OK, that's the problem when I'm working on my data. What about creating a temporary table in a script, alter the resultant table to add a generated column eg (totally untested)

create temporary table xyz as
SELECT tbl2.Name, SUM(tbl1.SumPay)
FROM tbl1
LEFT JOIN tbl2 ON ...
GROUP BY tbl2.Name
WITH DATA

ALTER temporary table xyz add column "_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 1) NOT NULL

Roy Lambert [Team Elevate]

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
>
> OK, that's the problem when I'm working on my data. What about creating a
> temporary table in a script, alter the resultant table to add a generated
> column eg (totally untested)
>
> create temporary table xyz as
> SELECT tbl2.Name, SUM(tbl1.SumPay)
> FROM tbl1
> LEFT JOIN tbl2 ON ...
> GROUP BY tbl2.Name
> WITH DATA
>
> ALTER temporary table xyz add column "_ID" INTEGER GENERATED BY DEFAULT AS
> IDENTITY (START WITH 100, INCREMENT BY 1) NOT >NULL

Thanks Roy

The solution that John suggest, on the next replay, based on your solution,
solve the problem without a temporary table.

Thanks
Sorin