CODEWITHSUNDEEP
sqloracle-database
Ayub
Ayub

Reputation: 599

sql query to sum and insert in another table

As per the schema defined in sqlfiddle I want to get the sum of credits - sum of debits and then insert this for each customer number is another table as customer number, balance. any clues how to do it, I tried the query in the fiddle but it does not seem to work

Upvotes: 0

Views: 37

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

Your problem is that you've confused ACCOUNT_TYPE and TRANSACTION_TYPE in your ACCOUNT_TRANSACTIONS table - you put the transaction types (credit, debit) in the ACCOUNT_TYPE field, and you put the account types (customer, etc) in the TRANSACTION_TYPE field.

If you switch the ACCOUNT_TYPE and TRANSACTION_TYPE fields in your table definition you get:

create table account_transactions
  (customer_number varchar2(10),
   transaction_type varchar2(10),
   account_type varchar2(10),
   amount number);

insert into account_transactions values('5555','credit','customer',10);
insert into account_transactions values('5555','debit','customer',10);
insert into account_transactions values('5555','debit','other',15);
insert into account_transactions values('5555','credit','mgr',15);

Here's a fiddle with the fields flipped around

With the table populated correctly your query

SELECT t1.CUSTOMER_NUMBER, sum(t2.amount - t1.amount) AS balance
  FROM ACCOUNT_TRANSACTIONS t1,
       ACCOUNT_TRANSACTIONS t2
  where t1.CUSTOMER_NUMBER=t2.CUSTOMER_NUMBER and 
        t1.TRANSACTION_TYPE = 'debit' AND
        t2.TRANSACTION_TYPE= 'credit' and
        t1.ACCOUNT_TYPE='customer' and
        t2.ACCOUNT_TYPE='customer'
  GROUP BY t1.CUSTOMER_NUMBER

works fine.

Best of luck.

Upvotes: 1

Related Questions