Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31

    Unanswered: SUM / MAX Problem after GROUPING

    Hi!

    I have following sql statement...

    Code:
    SELECT r.FALL, r.BETNR, r.VDI2, SUM(r.EES) AS sumEES
    FROM [FZG] f INNER HASH JOIN [FZGA] a ON f.FALL = a.FALL AND f.BETNR = a.BETNR
    INNER HASH JOIN [REKO] r ON r.FALL = f.FALL AND r.BETNR = f.BETNR
    WHERE a.MAXDEFZ LIKE 'F[1-4]'
    GROUP BY r.FALL, r.BETNR, r.VDI2
    ORDER BY r.FALL, r.BETNR, r.VDI2
    and get a table like...
    Code:
    FALL | BETNR | VDI2 | sumEES
    ------------------------------
    1000002	1	1	42
    1000002	1	2	5
    1000002	1	4	5
    1000013	1	1	80
    1000014	2	1	35
    1000015	1	1	10
    1000021	2	1	30
    1000022	1	1	42
    1000022	2	1	42
    1000023	1	1	20
    How can I get now only the lines with the maximum sumEES for each BETNR and FALL?

    Must look like...
    Code:
    FALL | BETNR | VDI2 | sumEES
    ------------------------------
    1000002	1	1	42
    1000013	1	1	80
    1000014	2	1	35
    1000015	1	1	10
    1000021	2	1	30
    1000022	1	1	42
    1000022	2	1	42
    1000023	1	1	20
    Is it possible to enhance my statement above like MAX(SUM(r.EES))

    Thank you!
    Last edited by sp00ky; 06-24-09 at 13:47.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You should try something like:

    SELECT v.fall, v.betnr, max(v.sumEES)
    FROM
    (
    your_above_SELECT
    ) v
    GROUP BY v.fall, v.betnr
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    Thank you that works perfect... but how can I return also the associated VDI2 value for max(v.sumEES)?

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I am not a T-SQL guru, but:

    SELECT v2.fall, v2.betnr, v2.vdi2, v2.sum_val
    FROM
    (
    vSELECT v.fall, v.betnr, max(v.sumEES) as max_val
    FROM
    (
    your_above_SELECT
    ) v
    GROUP BY v.fall, v.betnr
    ) v1,
    (
    your_above_SELECT
    ) v2
    WHERE
    v1.fall = v2.fall
    AND v1.betnr = v2.betnr
    AND v1.max_val = v2.sumEES
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    I got it allready, but thank you anyway...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •