Home » SQL & PL/SQL » SQL & PL/SQL » sum over multiple tables (oracle 9)
sum over multiple tables [message #444410] Sun, 21 February 2010 14:21 Go to next message
nsnvc
Messages: 3
Registered: February 2010
Junior Member
I'm trying to do a sum over 2 different tables but can't get it to work...
This is the idea:
I have a table A with client ID, time-id (per day), purchase amount and segment code.
In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.
This is what I have now:

select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where
A.client_id = B.client_id
and time_id between 20090101 and 20091001
and B.segment_code = 'A'

This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.
Any help is welcome!
Re: sum over multiple tables [message #444411 is a reply to message #444410] Sun, 21 February 2010 14:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: sum over multiple tables [message #444412 is a reply to message #444410] Sun, 21 February 2010 16:22 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use an outer-join and an nvl.
Re: sum over multiple tables [message #444423 is a reply to message #444410] Sun, 21 February 2010 22:58 Go to previous messageGo to next message
alee_vip
Messages: 1
Registered: February 2010
Junior Member
so good,,,
Re: sum over multiple tables [message #444424 is a reply to message #444423] Sun, 21 February 2010 23:04 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
alee_vip wrote on Sun, 21 February 2010 20:58
so good,,,



and time_id between 20090101 and 20091001


so BAD!

NUMBER is not DATE!
Previous Topic: dynamic collection set
Next Topic: Saving procedures with compilation erros
Goto Forum:
  


Current Time: Sun Dec 04 04:51:41 CST 2016

Total time taken to generate the page: 0.11932 seconds