Re: SQL and a Running/Moving Total

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Actually, this should be fine

select a.name, a.year, a.amt + b.amt + c.amt as TotalAmt
from myfile a
        inner join myfile b on a.name = b.name and a.year = (b.year + 1)
        inner join myfile c on a.name = c.name and a.year = (c.year + 2)

No need for the sum function...

Charles

On Thu, Oct 8, 2009 at 3:57 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
> select a.name, a.year, sum(a.amt + b.amt + c.amt)
> from myfile a
>         inner join myfile b on a.name = b.name and a.year = (b.year + 1)
>         inner join myfile c on a.name = c.name and a.year = (c.year + 2)
> group by a.name, a.year
>
> Performance is not going to be great.
> RPG records level access or an SQL cursor may be a better solution.
>
> HTH,
> Charles
>
> On Thu, Oct 8, 2009 at 3:40 PM, Mark Allen <scprideandms@xxxxxxxxx> wrote:
> > Table has 3 columns,Name,year,count  and I need a total of the "count" field
> > for each name over a 3 yr period, if there are NOT 3 years available then no
> > output row.
> >
> > Example:
> > Bill  1990    72
> > Bill  1991    73
> > Bill  1992    71
> > Bill  1993    55
> > Bill  1994    78
> > Bill  1995    73
> > Stan  1990    130
> > Stan  1991    122
> > Stan  1992    100
> > Stan  1993    115
> > Stan  1994    101
> > Stan  1995    112
> >
> > What I want from the SQL output  is
> >
> > Bill  1990    216
> > Bill  1991    199
> > Bill  1992    204
> > Bill  1993    206
> > Stan  1990    352
> > Stan  1991    337
> > Stan  1992    316
> > Stan  1993    328
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.