Re: SQL ? - summing same field, two results

Luis,
You have the problem scenario correct.
I can break it apart and get the results that I need, but running the full
statement returns no records.
Still, what you have provided helps, I'll keep testing this, may use an
interim view, but you have me on the right track.
Thanks,
Diane



From: "Luis Rodriguez" <luisro58@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 11/08/2018 11:56 AM
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