# Thread: How to group by SUM and MAX?

1. Registered User
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. Registered User
Join Date
Apr 2008
Location
Iasi, Romania
Posts
579
You should try something like:

SELECT v.fall, v.betnr, max(v.sumEES)
FROM
(
your_above_SELECT
) v
GROUP BY v.fall, v.betnr

3. Registered User
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. Registered User
Join Date
Apr 2008
Location
Iasi, Romania
Posts
579
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

5. Registered User
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
•