Returning max summarized amounts

[jmb2010]

My data looks like:
CO PID SID AMT
A12 Z123 1234 200
A12 Z123 1234 300
A12 Z123 5678 400

I need to retrieve the SID that has the maximum amount of the CO/PID/SID group combination and use that value to update a field on another table. So in the example above, I need the sub query to return the value 1234, since that would have the highest amount when summarized.

I thought I could select the SID where MAX(SUM(AMT)), but I get a -112, when using MAX and SUM. Would anyone have a suggestion on how to get the SID with max summarized AMT? Thx.

[patjones]

Hi jmb -

I only have read authorization for DB2 where I work, and cannot create a mock table. But, see if this works...

SELECT SID, SUM(AMT)
FROM tblTest
GROUP BY CO, PID, SID
HAVING SUM(AMT) = (SELECT MAX(tblA.SumOfAMT)
                   FROM (SELECT Sum(tblTest.AMT) AS SumOfAMT
                         FROM tblTest
                         GROUP BY tblTest.CO, tblTest.PID, tblTest.SID) AS tblA);

Here you would replace 'tblTest' with whatever your table name is. Let me know how it goes.

Pat