How to calculate the values from two tables in mysql

Question

0.00/5 (No votes)

See more:

I am having a table in mysql like this

Table1 name: credit

Date          Company Name        Amount
2013-12-11    smevc               500
2013-12-21    smevc               500
2013-12-21    smevc               1000
2013-12-21    smevc               6726
2013-12-21    smevc               8354.40
2013-12-20    smevc               700
2013-12-22    sec                 3736
2013-12-23    ccs                 3000

Table2 name: debit

Date         company_name         deb_amt
2013-12-24   smevc                1000
2013-12-22   smevc                2000
2013-12-22   ccs                  2000

Now i want to take difference calculation here i.e., Amount - deb_amt
Now
I need a output like

comp_name  bal_amt
smevc      14780.40
sec        3736
ccs        1000

Remember sec is available only in table 2 debit.. not in table 1 credit..
I got the output but the value of sec was displayed as null.
i used this query

SQL

SELECT cm.company_name AS comp_name,
((SELECT SUM(credit_amount) FROM credit_details c WHERE c.company_name = cm.company_name GROUP BY c.company_name) -
(SELECT SUM(debit_amount) FROM debit_details d WHERE d.company_name = cm.company_name GROUP BY d.company_name)) AS bal_amt
FROM credit_details cm GROUP BY cm.company_name
ORDER BY bal_amt DESC

pls correct my query to get the output.
Thanks,
Siva

Posted 28-Dec-13 20:31pm

sivamzcet

Updated 28-Dec-13 22:55pm

Aarti Meswania

v2

Add a Solution

1 solution

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)

GuyThiebaut · Accepted Answer · 2013-12-28T20:56:00

Solution 1

It's not an elegant solution but it may work...

SQL

select 
c.company,
c.amount - ifnull(d.debit,0) balance
from
(
select 
company,sum(amount) amount
from credit
group by company
)c
left join
(
select 
company, sum(deb_amt) debit
from debit
group by company
)d
on a.company = d.company

Posted 28-Dec-13 20:56pm

GuyThiebaut

Updated 28-Dec-13 22:55pm

v2

Comments

sivamzcet 29-Dec-13 3:11am

query works but the company name sec and its balance was not came

GuyThiebaut 29-Dec-13 3:50am

select
c.company,
c.amount - isnull(d.debit,o) balance
from
(
select
company,sum(amount) amount
from credit
group by company
)c
left join
(
select
company, sum(deb_amt) debit
from debit
group by company
)d
on a.company = d.company

sivamzcet 29-Dec-13 4:39am

#1582 - Incorrect parameter count in the call to native function 'isnull'
this error was came.. wat to do now??

sivamzcet 29-Dec-13 4:48am

okay i got.. its ifnull condition not isnull :)
Thanks for your reply
Stay in touch with us :)

GuyThiebaut 29-Dec-13 4:56am

I have amended the original solution.

sivamzcet 29-Dec-13 5:03am

one more doubt.. now i need to calculate only 2 no's after decimal
i.e if the value of balance=4956.74852
then i need the output like balance=4956.74
can u pls give me a solution?

GuyThiebaut 29-Dec-13 5:14am

Google broken again?
http://lmgtfy.com/?q=mysql+decimal+2+places

sivamzcet 29-Dec-13 5:22am

in query is it possible to get value?

GuyThiebaut 29-Dec-13 5:30am

What do you mean by 'value'?

sivamzcet 29-Dec-13 5:34am

thanks fr ur rep.. i got the ans :)
thanks fr ur help :)
stay with us :)