× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.