Aggregate on an aggregate

I need to get the sum of a field that already has an aggregate function (MAX) performed on it. I am using the following query

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty",

"tPCT"."BatchKey"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "tPCT"."BatchKey"

It yields the following results

ItemID Short Description Freeze Qty Current Qty BatchKey 3002954 SET, WRENCH HEX METRIC -33 129 42221 3002954 SET, WRENCH HEX METRIC 51 129 42244 3002954 SET, WRENCH HEX METRIC -31 129 42250

I need to SUM the maximum freeze quantity values per item ID. Therefore for this record, I need the following results:

3002954 SET, WRENCH HEX METRIC -13 129

Can this be done via a subquery? Any assistnance would be greatly appreciated?

Thanks,

DLee

I would think you could create a subquery using the following;

SELECT "ItemID","Short Description","Current Qty", SUM("Freeze Qty")

FROM ("vSS");

|||

Donna, try this query

SELECT ItemID

, MAX(SQ.ShortDesc) as [Short Description]

, SUM(SQ.FreezeQty) as [Freeze Qty]

, MAX(SQ.[Current Qty]) as [Current Qty]

FROM (

SELECT tI.ItemID

, MAX(vSS.ShortDesc)

, MAX(tPCT.FreezeQty)

, SUM(vSS.QtyOnHand)

, tPCT.BatchKey

FROM vSS INNER JOIN tI

ON vSS.ItemKey=tI.ItemKey

LEFT OUTER JOIN tPCT

ON vSS.ItemKey=tPCT.ItemKey

WHERE vSS.ItemID = '3002954'

GROUP BY tI.ItemID, tPCT.BatchKey

) SQ

GROUP BY SQ.ItemID

|||

Hello Donna,

Tweak your query a little and try this...

SELECT "tI"."ItemID", "vSS"."ShortDesc" "Short Description",

SUM("tPCT"."FreezeQty") "Freeze Qty", "vSS"."QtyOnHand" "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "vSS"."ShortDesc", "vSS"."QtyOnHand"

Hope this helps.

Regards.....

|||

If you do not need BatchKey, you can just remove it from the query and you should get the desired answer.

e.g.

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID"

|||

This did the trick!

Thanks Gopi!