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