Re: Help with SQLRPGLE statement

["Mike Naughton" <mnaughton@xxxxxxxxxxxx>]

Hi Tom,

I assume you mean you want to group the IDs and then show the sum of BAL1,
the sum of BAL2, and the sum of BAL3?
If so, this should work:

SELECT ID, SUM(BAL1) as BAL1SUM, SUM(BAL2) as BAL2SUM, SUM(BAL3) as BAL3SUM
FROM <wherever>
GROUP BY ID
ORDER BY <whatever>

Since you are using a column function (SUM()), you have to use GROUP BY
for each field in your SELECT statement that does not have a column
function applied to it (otherwise, you'll get an error).

hth

RPG400-L@midrange.com writes:
>I'm working on an SQLRPGLE program.  I have a file with the following 
>structure (simplified): 
>ID (2 alpha) 
>BAL1 (11.2) 
>BAL2 (11.2) 
>BAL3 (11.2) 
>
>What I need to do is come up with an SQL statement that will group all of 
>the IDs, and show the sum of BAL1, BAL2, BAL3 fields for each ID: 
>bb,100.00,200.00,300.00 
>EX,100.00,200.00,300.00 
>WA,50.00,0,50.00 
>
>I've looked in the "DB2 UDB for AS/400 SQL Programming" online manual,
>but 
>the examples for "Group By" are GIFs which don't display. 
>
>
>Tom Hightower
>Solutions, Inc
>http://www.simas.com


Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---