how to get SUM of two different values from same table

Question

1.00/5 (1 vote)

See more:

hi all...

i want get SUM of values from same table but two different conditions in mysql, the sample table is here...

 --------------------------------------
  credit_id | debit_id | Amount         
 --------------------------------------
|  1           4          10           |
|  1           3          10           |
|  1           2          10           |
|  2           1          20           |
|  3           1          20           |
|  5           1          20           |
----------------------------------------

step1:
First get the SUM(Amount) where credit_id=`1`;
The result is 30.

step2:
Second get the SUM(Amount) where debit_id=`1`;
The result is 60.

step3:
The final step is step2 - step1
That is 60-30=30.

How to get this result in single MySql query..
please help me....

Posted 23-Nov-13 19:30pm

sv sathish

Updated 23-Nov-13 20:34pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2013-11-23T22:41:00

Solution 2

Just back from a jog, a quick solution here:

SQL

select (select sum(amount) from table3 where credit_id ='5') -
(SELECT IF((select count(*) from table3 WHERE debit_id='5') > 0, (select sum(amount) from table3 where debit_id ='5'), 0))

Posted 23-Nov-13 22:41pm

Peter Leow

Comments

sv sathish 24-Nov-13 9:00am

Thank you so mush sir;)

Peter Leow · Accepted Answer · 2013-11-23T19:50:00

Solution 1

select (select sum(Amount) from table3 where debit_id ='1') - (select sum(Amount) from table3 where credit_id ='1')

Posted 23-Nov-13 19:50pm

Peter Leow

Comments

sv sathish 24-Nov-13 2:00am

Thank you sir:)

Peter Leow 24-Nov-13 2:04am

You are welcome.

sv sathish 24-Nov-13 2:32am

Sir i want another help
You See the same table,the credit_id=5 means the result is NULL.
i want the result,For example the credit_id is 5 then

SUM(Amount) where credit_id=`5` - SUM(Amount) where debit_id=`5`;
i expected result is 20...
how it's possible...