Re: SQL ? - summing same field, two results

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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