× 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.



Hi Phil

I am intrigued by this one - there are a few techniques for getting running 
totals and subtotals - what you want is a little different - subtotals are 
often a separater record from the data, as when you use Query/400.

With SQL, one technique is to use UNIONs - in this case, you really have 2 
distinct but related recordsets that you want to combine:
1. Records in groups by ACCT & INV# that do NOT have an AMT = max(AMT) -> no 
subtotal
2. Records that ARE the max(AMT) record -> subtotal

I've assumed char(3) for acct & inv#, decimal(7, 2) for amt.

Number 1 can be done this way - there are other ways, such as an exception join 
with a common or nested table expression:

select ACCT, INV#, AMT, decimal(0, 9, 2) TOTAMT
from PKTEST O
where AMT not in
(select max(AMT)
from PKTEST
where ACCT = O.ACCT and INV# = O.INV#)

Number 2 can be done this way - 

select ACCT, INV#, max(AMT) AMT, decimal(sum(AMT), 9, 2) TOTAMT
from PKTEST
group by ACCT, INV#

So to get what you want, do a UNION:

select ACCT, INV#, AMT, decimal(0, 9, 2) TOTAMT
from PKTEST O
where AMT not in
(select max(AMT)
from PKTEST
where ACCT = O.ACCT and INV# = O.INV#)

union
select ACCT, INV#, max(AMT) AMT, decimal(sum(AMT), 9, 2) TOTAMT
from PKTEST
group by ACCT, INV#

You did not specify how to sort it - your example suggests that you want it 
sorted by AMT with a given ACCT & INV#. 

To have AMT in possible arrival sequence, use

order by ACCT, INV#, TOTAMT

Actually the UNION seemed to force the following sort, but i don't know if that 
is guaranteed.


To have AMT in ascending order, use

order by ACCT, INV#, TOTAMT, AMT

There you go - divide and conquer!!

HTH
Vern

-------------- Original message -------------- 
From: "Phil Kestenbaum" <pkestenbaum@xxxxxxxxxxx> 



HI, I would like to do this in an SQL statement: 



I want to take a record that has 

ACCT INV# AMT TOTAMT 



Where totamt is created in the SQL 

When there is a break, a new ACCT INV. 



Only when there is a new Acct/Inv then the prev. record should contain 
the total of all the prev Acct/Inv Amt's in Totamt. 



So for example: 

Acct INV Amt Totamt 

123 001 1.00 0.00 

123 001 2.00 0.00. 

123 001 3.00 6.00 

123 002 1.00 0.00 

Etc. 



Thanks, 
Phil 




This message contains information proprietary to our company. It is intended 
to 
be read only by the individual or entity named above or their designee. 
Any distribution of this message or the information contained herein without 
written permission from our company is strictly prohibited. If the reader of 
this message is not the intended recipient or an agent responsible for 
delivering it to the intended recipient, you are hereby notified that you 
have 
received this document in error and that any review, dissemination, 
distribution, or copying of this message is strictly prohibited. If you have 
received this communication in error, please notify us immediately by e-mail, 
and delete the original message. 


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list 
To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
or email: RPG400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/rpg400-l. 

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.