OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Help SUM (Oracle, 8.1.7.0.0,Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help SUM [message #623412]

Wed, 10 September 2014 03:45

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Dear All,

I know I am using outdated version of Oracle but what to do my company is in the process of updating to 11g by end of this year. I need your help in sum query. I have created to dummy tables and the required result I needed.

CREATE TABLE DEVL_PO_ITEMS
(
  PO_NO   VARCHAR2(10),
  JOB_NO  VARCHAR2(5),
  QTY     NUMBER,
  PRICE   NUMBER
);

Insert into DEVL_PO_ITEMS
   (PO_NO, JOB_NO, QTY, PRICE)
 Values
   ('12345', '1', 2, 10);
Insert into DEVL_PO_ITEMS
   (PO_NO, JOB_NO, QTY, PRICE)
 Values
   ('12345', '2', 2, 5);
Insert into DEVL_PO_ITEMS
   (PO_NO, JOB_NO, QTY, PRICE)
 Values
   ('12345', '3', 4, 10);
Insert into DEVL_PO_ITEMS
   (PO_NO, JOB_NO, QTY, PRICE)
 Values
   ('123456', '1', 4, 2);
Insert into DEVL_PO_ITEMS
   (PO_NO, JOB_NO, QTY, PRICE)
 Values
   ('123456', '2', 3, 3);
Insert into DEVL_PO_ITEMS
   (PO_NO, JOB_NO, QTY, PRICE)
 Values
   ('1234', '1', 10, 10);
Insert into DEVL_PO_ITEMS
   (PO_NO, JOB_NO, QTY, PRICE)
 Values
   ('123', '10', 10, 40);
COMMIT;

CREATE TABLE DEVL_PO_SUPP
(
  PO_NO   VARCHAR2(10),
  INV_NO  VARCHAR2(5),
  AMT     NUMBER
);

Insert into DEVL_PO_SUPP
   (PO_NO, INV_NO, AMT)
 Values
   ('12345', '90', 15);
Insert into DEVL_PO_SUPP
   (PO_NO, INV_NO, AMT)
 Values
   ('12345', '91', 10);
Insert into DEVL_PO_SUPP
   (PO_NO, INV_NO, AMT)
 Values
   ('123456', '10', 3);
Insert into DEVL_PO_SUPP
   (PO_NO, INV_NO, AMT)
 Values
   ('123456', '11', 2);
Insert into DEVL_PO_SUPP
   (PO_NO, INV_NO, AMT)
 Values
   ('1234', '20', 10);
COMMIT;

I have two tables po items and supplier inv table. I want to sum the amount in po items for the unique po no and the same I want to link to supplier inv table and get the sum of amount from supplier table.

PO ITEMS
PO_NO	AMT
123	400
1234	100
12345	70
123456	17

SUPPLIER 
PO_NO	SUM(AMT)
1234	10
12345	25
123456	5

Now I want to link both these tables and want the result like this

PO_NO	AMT	PO_NO	SUM(AMT)
123	400	0	0
1234	100	1234	10
12345	70	12345	25
123456	17	123456	5

Can anyone help me?

[Edit MC: remove schema name and storage parameters in CREATE TABLE; add code tags in results; remove unnecessary empty lines]

[Updated on: Wed, 10 September 2014 03:53] by Moderator

Report message to a moderator

Re: Help SUM [message #623413 is a reply to message #623412]

Wed, 10 September 2014 03:59

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please do not put schema name and storage parameters in your test case, we have not the same ones than you.

SQL> with
  2    sum1 as (
  3      select po_no, sum(qty*price) amt
  4      from DEVL_PO_ITEMS
  5      group by po_no
  6    ),
  7    sum2 as (
  8      select po_no, sum(amt) amt
  9      from DEVL_PO_SUPP
 10      group by po_no
 11    )
 12  select i.po_no, i.amt, nvl(s.po_no,0) po_no, nvl(s.amt,0) amt
 13  from sum1 i left outer join sum2 s on s.po_no = i.po_no
 14  order by 1
 15  /
PO_NO             AMT PO_NO             AMT
---------- ---------- ---------- ----------
123               400 0                   0
1234              100 1234               10
12345              70 12345              25
123456             17 123456              5

Report message to a moderator

Re: Help SUM [message #623414 is a reply to message #623413]

Wed, 10 September 2014 04:02

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

WITH factoring clause won't work in 8i, so ... slightly rewritten:

SQL>   SELECT i.po_no ino,
  2           iamt,
  3           NVL (s.po_no, 0) sno,
  4           NVL (samt, 0) samt
  5      FROM (  SELECT s.po_no, SUM (s.amt) samt
  6                FROM devl_po_supp s
  7            GROUP BY s.po_no) s,
  8           (  SELECT i.po_no, SUM (qty * price) iamt
  9                FROM devl_po_items i
 10            GROUP BY i.po_no) i
 11     WHERE i.po_no = s.po_no(+)
 12  ORDER BY i.po_no;

INO              IAMT SNO              SAMT
---------- ---------- ---------- ----------
123               400 0                   0
1234              100 1234               10
12345              70 12345              25
123456             17 123456              5

SQL>

Report message to a moderator

Re: Help SUM [message #623428 is a reply to message #623414]

Wed, 10 September 2014 05:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You're right, I completely forgot this point, thanks to rewrite my query in the old way.

Report message to a moderator

Previous Topic:	Parallel hint issue
Next Topic:	query to find two highest salaries in each department

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:06:29 CDT 2024