[SQL] Problem with group by and max

[Stefan Bauer]

Hi NG,

I have problem... I'm currently using UDB v8.1 for Linux.

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.

[Stefan M. Mihokovic]

Test this / Probier mal das

select t1.id1
, t1.id2
, sum ( t1.value ) as value

from test as t1

where ( t1.id1, t1.id2 ) in ( select t2.id1
, max ( t2.id2 ) as id2
from test as t2
group by t2.id1
)

group by t1.id1
, t1.id2
;

Regards / Gruß

----------------------------------

Stefan M. Mihokovic

email: st***@stemi.de

----------------------------------
"Stefan Bauer" <st***********@ yahoo.de> schrieb im Newsbeitrag
news:bo******** *****@ID-209925.news.uni-berlin.de...

Hi NG,

I have problem... I'm currently using UDB v8.1 for Linux.

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.

[Mauro Cazzari]

You can also try either one of these two statements:

with maxval as
(
select id1, max(id2) as id2
from test
group by id1
)
select maxval.id1, maxval.id2, sum(value)
from maxval, test
where test.id1 = maxval.id1
and test.id2 = maxval.id2
group by maxval.id1, maxval.id2;

or

select maxval.id1, maxval.id2, sum(value)
from test, (select id1, max(id2) as id2
from test
group by id1) as maxval
where test.id1 = maxval.id1
and test.id2 = maxval.id2
group by maxval.id1, maxval.id2;

Enjoy,
Mauro.
"Stefan M. Mihokovic" <ne**@stemi.d e> wrote in message
news:bo******** *****@news.t-online.com...

Test this / Probier mal das

select t1.id1
, t1.id2
, sum ( t1.value ) as value

from test as t1

where ( t1.id1, t1.id2 ) in ( select t2.id1
, max ( t2.id2 ) as id2
from test as t2
group by t2.id1
)

group by t1.id1
, t1.id2
;

Regards / Gruß

----------------------------------

Stefan M. Mihokovic

email: st***@stemi.de

----------------------------------
"Stefan Bauer" <st***********@ yahoo.de> schrieb im Newsbeitrag
news:bo******** *****@ID-209925.news.uni-berlin.de...

Hi NG,

I have problem... I'm currently using UDB v8.1 for Linux.

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.

[Tokunaga T.]

Other statements:
SELECT id1 , id2 , SUM(value) AS sum
FROM test T1
WHERE T1.id2 = (SELECT MAX(id2)
FROM test T2
WHERE T1.id1 = T2.id1
)
GROUP BY id1 , id2
;

and

SELECT id1 , id2 , SUM(value) AS sum
FROM (SELECT id1 , id2 , value
, DENSE_RANK() OVER(PARTITION BY id1 ORDER BY id2 desc) as drank
FROM test
) T
WHERE drank = 1
GROUP BY id1 , id2
;

[Tokunaga T.]

Some more statements.
Just my cuoriosity.
SELECT id1 , id2 , SUM(value) AS sum
FROM test T1
WHERE NOT EXISTS
(SELECT *
FROM test T2
WHERE T1.id1 = T2.id1
AND T1.id2 < T2.id2
)
GROUP BY id1 , id2
;

and

SELECT T1.id1 , T1.id2 , SUM(T1.value) AS sum
FROM test T1
LEFT OUTER JOIN
test T2
ON T1.id1 = T2.id1
AND T1.id2 < T2.id2
WHERE T2.id2 IS NULL
GROUP BY T1.id1 , T1.id2
;

and

SELECT id1 , id2 , sum_value
FROM (SELECT id1 , id2 , sum_value
, ROWNUMBER() OVER(PARTITION BY id1 ORDER BY id2 desc) AS rownum
FROM (SELECT id1 , id2 , SUM(value) AS sum_value
FROM test
GROUP BY id1 , id2
) AS X
) AS Y
WHERE rownum = 1
;

(correction(or addition) to my previous post)
RANK() can be used instead of DENSE_RANK().
SELECT id1 , id2 , SUM(value) AS sum
FROM (SELECT id1 , id2 , value
, RANK() OVER(PARTITION BY id1 ORDER BY id2 desc) as rank
FROM test
) T
WHERE rank = 1
GROUP BY id1 , id2
;