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.

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