Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Arrow Unanswered: subquery + group by + max = :-(

    Hi NG,

    I have problem...

    Here is the table "test":

    ID1 ID2 ID3 VALUE
    ----------- ----------- ----------- -----------
    1 0 1 23
    1 0 2 9
    1 1 1 34
    1 1 2 12
    2 0 1 56
    2 0 2 13
    2 2 1 98
    2 2 2 24

    For each id1 the query should return the biggest id2 and calculate the sum of VALUES over all id3.

    This would be the result:

    ID1 ID2 SUM
    ----------- ----------- ---------
    1 1 46
    2 2 122

    For id1=1 is id2=1 the biggest id2 value. The sum is 34+12=46.
    For id1=2 is id2=2 the biggest id2 value. The sum is 98+24=122.

    I tried it with this query:

    select t1.id1, t1.id2, sum(t1.value)
    from test t1
    group by t1.id1
    having t1.id2=(select max(t2.id2) from test t2 where t1.id1=t2.id2)

    But I failed...

    I do appreciate everyone's help!

    S.B.

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Wink Use this

    Hello,

    you can use this statement

    SELECT id1, MAX(id2), MAX(sum_value) FROM (
    SELECT t1.id1, t1.id2, sum(t1.value) AS sum_value
    FROM test t1
    GROUP BY t1.id1, t1.id2
    )
    GROUP BY id1

    Hope that helps ?

    Best regards
    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com

Posting Permissions

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