Home » SQL & PL/SQL » SQL & PL/SQL » group by issue with adding another field (oracle 9.2)
group by issue with adding another field [message #348713] Wed, 17 September 2008 15:07 Go to next message
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 #348716 is a reply to message #348713] Wed, 17 September 2008 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

My green car works.
My white car doesn't work.
Tell me how to make my white car go.
Re: group by issue with adding another field [message #348720 is a reply to message #348716] Wed, 17 September 2008 15:24 Go to previous messageGo to next message
blakhama
Messages: 10
Registered: August 2006
Junior Member
anacedent wrote on Wed, 17 September 2008 16:15
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

My green car works.
My white car doesn't work.
Tell me how to make my white car go.


Laughing OMG, another post nazi, anyone else

FYI:

Some of the most important instructions are:

* Be polite!
* Never belittle anyone for asking beginner-level questions or for their English skills.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines

Re: group by issue with adding another field [message #348753 is a reply to message #348720] Wed, 17 September 2008 20:51 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Considering the level of information supplied and the type of question, the answer given was pretty well spot on.

What is 'F4211_V_UQ'? Read the posting guidelines. Give us the 'table' definition. Format your query. Show us the error that you are receiving.

David
Re: group by issue with adding another field [message #349284 is a reply to message #348713] Fri, 19 September 2008 11:37 Go to previous messageGo to next message
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.
Re: group by issue with adding another field [message #349415 is a reply to message #349284] Sun, 21 September 2008 11:37 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
When my eyes were enlightened to Oracle Analytic functions I resolved many GROUP-BY problems and simplified many work-around
queries I had in place.

Good article on this site by Shouvic Basu:
http://orafaq.com/node/55

Also, Art Trifinov, see his section on
Modularizing Complex SQL:

http://www.orafaq.com/node/1884

And a searh list of links to reference material:

http://search.yahoo.com/search?p=%2Boracle+%2Banalytic+%2Bfunctions+&fr=yfp-t-501&toggle=1&cop=mss&ei=UTF-8

Regards
Harry
Previous Topic: oracle sequence
Next Topic: Executing dynamic SQL statements
Goto Forum:
  


Current Time: Fri Dec 02 20:32:25 CST 2016

Total time taken to generate the page: 0.35986 seconds