Hi guys,
sorry to trouble u guys,but can't resist
Actually i have a table (transaction table) in which i have got customer id(can have more than one record with same id) ,acc no, and type (either credit or debit). Now i want to find the balance of the customer by inputting account number
the table will look like
cust id acc no type amount
1010 2323 cr(credit) 2000
1020 2324 dr(debit) 3000
1010 2323 dr 4000
1020 2324 cr 2000
pls help me out
customer_id and account_number seem a little redundant to me.
SELECT sum(amount)
FROM transaction_table
WHERE acc_num = "2323";
I think you might need a GROUP BY clause in there as well. Though that won't give you the answer you need, maybe it'll help. I only basic SQL myself. I haven't quite learned PL/SQL yet, which is what I would use to use an IF statement to see whether to add or subtract the amount.
On second thought, I think you can do this with a nested query.
Hi
Thanks for ur help. But ur query wont work as the amount entered in the transaction table will be positive only. So we have to differentiate between the amt credited and debited.
The query u gave will just give me the sum even if the amt is debited
I have got the answer for that
select cus_id, acc_id, sum(decode(acc_type,\'dr\',balance*-1,balance)) from
test_acc
this will put a - sign wherever the type is debit temporarily. It doesnt effect ur original table
As u are a learner,this query will help you in future
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.