Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 14 of 14 total |
Add a counter to sql |
Mon, Aug 9 2010 11:54 AM | Permanent Link |
John Hay | 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 |
Mon, Aug 9 2010 1:25 PM | Permanent Link |
Hershcu Sorin | "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 |
Mon, Aug 9 2010 1:32 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Aug 10 2010 3:28 AM | Permanent Link |
Hershcu Sorin | "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 |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |