RE: SQL ? - summing same field, two results

Can be done easier:

With x as (Select TMDONR,
Sum(NullIf(TMAMNT, 0)) Total,
Min(Case When TMAMNT > 0 Then TMUPDTI End) MinDate
from MyFile
Group By TMDONR)
Select x.*, TMAMNT
from x join MyFile a on Mindate = TMUPDTI;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Luis
Rodriguez
Sent: Donnerstag, 8. November 2018 18:53
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL ? - summing same field, two results

Diane,

Ok. Let me see if I understood your problem correctly:

-- Let's suppose this is your table:
CREATE TABLE MYLIB.MYFILE (
TMDONR INT NOT NULL WITH DEFAULT
, TMAMNT DECIMAL(15, 2) NOT NULL WITH DEFAULT , TMUPDTI DATE NOT NULL WITH
DEFAULT
) ;

LABEL ON COLUMN MYLIB.MYFILE(
TMDONR TEXT IS 'Donor TMDONR'
, TMAMNT TEXT IS 'Amount'
, TMUPDTI TEXT IS 'Date'
);

Your data is like this:
Donor TMDONR Amount DATE
1234 $,00 01/20/1992
1234 $,00 06/14/1992
1234 $20,00 02/21/1999
1234 $10,00 04/03/1999
1234 $5,00 06/28/1999

As I read it, you wanted to have the date with the first amount donated.

I think you can have it with this statement:

WITH t1 AS (
SELECT TMDONR, MIN(TMUPDTI) AS first_date FROM mylib.myfile WHERE tmamnt
<> 0 GROUP BY TMDONR
)
, t2 AS (
SELECT TMDONR, SUM(TMAMNT) AS tot_amount FROM mylib.myfile GROUP BY TMDONR
)
SELECT t1.TMDONR
, (
SELECT TMAMNT
FROM mylib.myfile
WHERE TMDONR = t1.TMDONR AND TMUPDTI = t1.first_date
) AS first_amount
, t1.first_date
, t2.tot_amount
FROM t1
INNER JOIN t2 USING (TMDONR)

Your output should be:

Donor ID FIRST_AMOUNT FIRST_DATE TOT_AMOUNT
1234 20 1999-02-21 35


You can convert your SQL statement as a view. Just write: CREATE VIEW
MYLIB.MYVIEW AS before your statement.


HTH,
Luis Rodriguez