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



Diane,

Got it. Anything else, please don't hesitate to ask.

Regards,
Luis

Sent from my Moto G phone. Please excuse my brevity.

On Fri, Nov 9, 2018, 10:25 <dmmueller@xxxxxxx wrote:

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

--





--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.