Home » SQL & PL/SQL » SQL & PL/SQL » group by in different table
group by in different table [message #435808] Fri, 18 December 2009 02:24 Go to next message
abc1982
Messages: 1
Registered: December 2009
Junior Member
I need to generate a set of data which display the sum of order quantity and receive quantity

Order
--------
Order_Num | Order_Line | Order_Qty 
A1        |   0001     |  100
A1        |   0002     |  200

Receive
-------
Receive_Num | Receive_Line | Receive_Qty | Order_Num | Order_Line 
R1          | 0001         | 50          | A1        | 0001

Expected Result:
Result
------
Order_Num | Receive_Num | Order_Qty | Receive_Qty
A1        | R1          | 300       | 50 

I have tried to write into the following statement:
select a.order_num, sum(a.order_qty), sum(b.receive_qty) from order a, receive b 
where a.order_num = b.order_num
group by a.order_num

the result will be 100 in receive_QTy, which is duplicate 2 time due to order table have 2 lines of record. I have tried also in the way of following:
select a.order_num, a.order_qty, sum(b.receive_qty) from order a, receive b 
where a.order_num = b.order_num
group by a.order_num, a.order_qty

but it seem that if there are 2 lines of record in Receive table with only 1 line of record in Order table, then this statement cannot be support.

Any idea? Thanks

[Updated on: Fri, 18 December 2009 02:53] by Moderator

Report message to a moderator

Re: group by in different table [message #435810 is a reply to message #435808] Fri, 18 December 2009 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: group by in different table [message #435811 is a reply to message #435808] Fri, 18 December 2009 02:53 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You apply SUM on both the tables seperately and then join both of them on common field.

Still you have any prob, then come back.

regards,
Delna
Re: group by in different table [message #435813 is a reply to message #435808] Fri, 18 December 2009 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can there be multiple receives for one order with same receive number?
If yes, try:
with 
  orders as (
    select order_num, sum(order_qty) sum_order_qty
    from order
    group by order_num
  ),
  receives as (
    select order_num, receive_num, sum(receive_qty) sum_receive_qty
    from receive
    group by order_num, receive_num
  )
select o.order_num, r.receive_num, o.sum_order_qty, r.sum_receive_qty
from orders o, receives r
where r.order_num = o.order_num
order by 1, 2
/

Regards
Michel
Re: group by in different table [message #435815 is a reply to message #435808] Fri, 18 December 2009 03:03 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
According to the data you have provided...

SQL>select * from t10;

  ORDER_NO      O_QTY
---------- ----------
         1        100
         1        200

2 rows selected.

SQL>select * from t11;

  ORDER_NO      R_QTY
---------- ----------
         1         50

1 row selected.

SQL>with source1 as
  2  (select order_no, sum(o_qty) expr1
  3  from t10
  4  group by order_no),
  5  source2 as
  6  (select order_no, sum(r_qty) expr2
  7  from t11
  8  group by order_no)
  9  select s1.order_no, s1.expr1, s2.expr2
 10  from source1 s1, source2 s2
 11  where s1.order_no = s2.order_no;

  ORDER_NO      EXPR1      EXPR2
---------- ---------- ----------
         1        300         50

1 row selected.


regards,
Delna
Re: group by in different table [message #435817 is a reply to message #435815] Fri, 18 December 2009 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Mine is better written. Razz

Regards
Michel
Re: group by in different table [message #435818 is a reply to message #435817] Fri, 18 December 2009 03:16 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
./fa/1626/0/

May I know how?

regards,
Delna
Re: group by in different table [message #435819 is a reply to message #435818] Fri, 18 December 2009 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indentation, name of columns and subqueries (was just a joke).

Regards
Michel
Re: group by in different table [message #435825 is a reply to message #435819] Fri, 18 December 2009 04:11 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
./fa/2115/0/

regards,
Delna
Previous Topic: counting files
Next Topic: sql trace
Goto Forum:
  


Current Time: Tue Dec 06 06:12:25 CST 2016

Total time taken to generate the page: 0.17471 seconds