group by issue with adding another field [message #348713] |
Wed, 17 September 2008 15:07  |
blakhama
Messages: 10 Registered: August 2006
|
Junior Member |
|
|
OK, I am trying to add the field SDAITM to identify the MAXITEMPRICE, however when I add the item field SDAITM , I lose the distinct benefit of the group by clause. THe query returns the rows I want, however, when I add SDAITM and add it to the group by all the records are returned:
This works but without SDAITM
select SDDOCO, sum(sduorg * sduprc) PRICE, max(sduprc) MAXITEMPRICE from F4211_V_UQ group by SDDOCO
This dosn't
select SDDOCO, SDAITM, sum(sduorg * sduprc) PRICE, max(sduprc) MAXITEMPRICE from F4211_V_UQ group by SDDOCO, SDAITM
SDDOC is the order number
the sum clause (alias PRICE) is the item qty x price -or- order total
MAX(sduprc) (MAXITEMPRICE) is the max price within the order number
Any suggestions?
thx...
|
|
|
|
|
|
Re: group by issue with adding another field [message #349284 is a reply to message #348713] |
Fri, 19 September 2008 11:37   |
blakhama
Messages: 10 Registered: August 2006
|
Junior Member |
|
|
Anyhow, I was able to solve. It's probably not posted correctly, I'd figure I'd share though:
I was asking to display this field distinctly: SDDOCO (which is an order number)
with the highest price item: SDUPRC
and include the order total: ORDERTOTAL
This works:
SELECT b.sdDoco,
b.sdaItm,
b.sdUprc,
c.OrderTotal
FROM (SELECT sdDoco,
sdaItm,
(sdUprc) sdUprc,
MAX(sdUprc) OVER(PARTITION BY sdDoco ) AS MaxPrice
FROM MY_TABLE) b,
(SELECT sdDoco,
SUM((sDuorg * (sdUprc))) OrderTotal
FROM MY_TABLE
GROUP BY sdDoco) c
WHERE sdUprc = MaxPrice
AND b.sdDoco = c.sdDoco;
Returned:
SDDOCO SDAITM SDUPRC ORDERTOTAL
---------- ------------------------- ---------- ----------
1110457 80641402 61024000 61024000
1260799 18111867 264366000 264366000
2677015 BR110011 1367110000 1410540000
2677016 28408255 4760800 6415200
2677017 17001001 1842400 1842400
2677018 BR110011 1339767800 1339767800
2677019 28408678 42561400 42561400
2677020 28408255 5301800 5301800
2677021 28408255 5301800 5301800
which is correct...
**************************************************************
The issue before was this query didn't return the results I was looking for:
SELECT sdDoco,
sdaItm,
SUM(sDuorg * sdUprc) SDUPRC,
MAX(sdUprc) MaxItemPrice
FROM MY_TABLE
GROUP BY sdDoco,
sdaItm
returned:
SDDOCO SDAITM SDUPRC ORDERTOTAL
---------- ------------------------- ---------- ------------
1110457 0 0
1110457 80641402 61024000 61024000
1260799 0 0
1260799 18111867 264366000 264366000
2677015 BR110011 1367110000 1367110000
2677015 28408678 43430000 43430000
2677016 17001001 1654400 1654400
2677016 28408255 4760800 4760800
2677017 17001001 1842400 1842400
2677018 BR110011 1339767800 1339767800
2677019 28408678 42561400 42561400
SDDOCO SDAITM SDUPRC MAXITEMPRICE
---------- ------------------------- ---------- ------------
2677020 28408255 5301800 5301800
2677021 28408255 5301800 5301800
which is incorrect...
Another issue is I am working with data from AS400/DB2 via the oracle transparent gateway, but I will not get into that right now.
Please note I am not an Oracle DBA, so I APOLOGIZE if I am not presenting Oracle terminology appropriately.
|
|
|
|