How to calculate 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   sec                  736
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        3000
ccs        1000

What query i need to use to get that output?
Thanks,
Siva

Posted 28-Dec-13 1:12am

sivamzcet

Updated 28-Dec-13 2:29am

Maciej Los

v2

Add a Solution

Comments

CHill60 28-Dec-13 7:32am

I see below that you have already tried something ... use the Improve question link above to post that attempt.
What is not clear is if the date is significant - or do you just want the total balance per company regardless of the date?

Maciej Los 28-Dec-13 8:29am

Please, use formattings ;)

Peter Leow 28-Dec-13 9:43am

Try solution 4.

4 solutions

Solution 1

You can Use JOIN between tables
check this link for more info

http://dev.mysql.com/doc/refman/4.1/en/join.html[^]

Posted 28-Dec-13 1:18am

Rana Zarour

Comments

sivamzcet 28-Dec-13 7:20am

yeh i knw joins should come here.. i tried but i cant get correct output. If u knw post the query here

Solution 2

Try this:

SQL

SELECT C.[Company Name] AS COMPNAME, SUM(C.Amount) AS AMT, SUM(D.DEB_AMT) AS DEBIT, SUM(C.Amount) - SUM(D.DEB_AMT) AS BALANCE
FROM Credit AS C INNER JOIN Debit AS D ON C.[Company Name] = D.[Company Name]
GROUP BY C.[Company Name]

For further information about JOIN's, please see: Visual Representation of SQL Joins[^]

Posted 28-Dec-13 2:34am

Maciej Los

Updated 28-Dec-13 22:24pm

v2

Comments

sivamzcet 29-Dec-13 1:38am

i got output for only smevc.. other companies details not comes.. wat to do?

Maciej Los 29-Dec-13 4:25am

I forgot to add GROUP BY statement. Please, have a look at updated answer.

sivamzcet 29-Dec-13 5:06am

no pblm i found the solution :)
thanks fr ur help
stay with us :)

Solution 3

SQL

SELECT * ,val-amountval FROM (select *, sum(`Amount`) val from credit group by  CompanyName) as c
left join
(select  Company_Name, sum(amount) amountval from debit group by Company_Name) as d on c.comname=d.comname

check column name that's the query

Posted 28-Dec-13 2:42am

Rana Zarour

Comments

CHill60 28-Dec-13 8:54am

This is not valid SQL ... did you run it?

Rana Zarour 28-Dec-13 9:48am

yes i did , its valid and work correctly but i told u to check column name as your table

sivamzcet 29-Dec-13 1:57am

i cant able to know diz query.. in ur query watz val-amountval?? i haven't any column like that name u mentioned

Rana Zarour 29-Dec-13 4:59am

its alias for sum(amount) where is your problem ??

write the complete create statement for your tables so i can update the query for you

sivamzcet 29-Dec-13 5:05am

hi rana zarour i found the solution :)
thanks for ur solution.. it too works.. no pblm in ur query :)

Maciej Los 29-Dec-13 5:08am

If it works, please mark His/Her answer as a solution (green button) ;)

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-12-28T03:40:00

Solution 4

No need join.

SQL

SELECT cm.`Company Name` AS comp_name,
((SELECT SUM(Amount) FROM credit c WHERE c.`Company Name` = cm.`Company Name` GROUP BY c.`Company Name`) -
(SELECT SUM(deb_amt) FROM debit d WHERE d.company_name = cm.`Company Name` GROUP BY d.company_name)) AS bal_amt
FROM credit cm GROUP BY cm.`Company Name`
ORDER BY bal_amt DESC

I just remember you are using MySql, it will work now. Try it out at sqlfiddle[^].

Posted 28-Dec-13 3:40am

Peter Leow

Updated 28-Dec-13 23:05pm

v2

Comments

sivamzcet 29-Dec-13 1:54am

i got the output for all the companies but except sec it displays null.. now wat to do?

Peter Leow 29-Dec-13 5:06am

I just realized that you are using mysql, it should work now. also try it out at the sqlfiddle link.