Home » SQL & PL/SQL » SQL & PL/SQL » Adding 2 SUM Functions from two tables (ORACLE 9i)
Adding 2 SUM Functions from two tables [message #296662] Mon, 28 January 2008 10:14 Go to next message
psun03
Messages: 2
Registered: January 2008
Junior Member
This is a query I've been messing with for a while now. I have two tables(table a, table b). I want to take the sum of the quantities of both and then add them together. Right now, I am doing is running two separate sql statements and moving the data to an external program and let it do the calculations, however; it is a long painful process and it just takes up too much time. My question is there a way i can get the sum from and 'A' and 'B' into a single query and add the quantities together. The statement following is an example:

select c.product_group, sum(b.trans_qty*-1)+sum(a.qty_order*-1), to_char(b.trans_date,'YYYY-MM') from table_c c, table_b b, table_a a where a.item_no = b.item_no and b.trans_type = 'S' and trans_date between to_date('01-01-2007','MM-DD-YYYY') and to_date('12-31-2007','MM-DD-YYYY') group by a.product_group, to_char(b.trans_date,'YYYY-MM')order by to_char(b.trans_date,'YYYY-MM')

sorry the code is a little messy. Is this possible with one statement? If so, then how? If not, then what would be a solution to this without having to run this by an external program?
Re: Adding 2 SUM Functions from two tables [message #296671 is a reply to message #296662] Mon, 28 January 2008 11:05 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

Sum will take an expression. This is what I tried :-

SQL> select * from junes1 order by col1;
 
      COL1 COL2                       COL3
---------- -------------------- ----------
         1 Kawasaki                   1000
         2 Kawasaki                    900
         3 Yamaha                     1000
 
SQL> select * from junes2 order by col1;
 
      COL1 COL2                       COL3
---------- -------------------- ----------
         1 lotsa mods                  160
         2 no mods                       0
         3 tiny mods                    50
 
SQL>

  1  select sum(junes1.col3 + junes2.col3)
  2  from junes1, junes2
  3  where junes1.col1 = junes2.col1
  4* group by junes1.col1
SQL> /
 
SUM(JUNES1.COL3+JUNES2.COL3)
----------------------------
                        1160
                         900
                        1050



Hopefully this helps?

You can use code tags to make you code a little more readable. Use the curly brackets icon to insert the code tags when posting.
Re: Adding 2 SUM Functions from two tables [message #296675 is a reply to message #296671] Mon, 28 January 2008 11:11 Go to previous messageGo to next message
psun03
Messages: 2
Registered: January 2008
Junior Member
Umm.. duh Embarassed
Re: Adding 2 SUM Functions from two tables [message #296677 is a reply to message #296662] Mon, 28 January 2008 11:24 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To help you to know how to post: 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 the "Preview Message" button to verify.
Also post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Hierarchical Query question
Next Topic: replace function
Goto Forum:
  


Current Time: Sat Dec 03 10:18:53 CST 2016

Total time taken to generate the page: 0.08034 seconds