SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using two Sum function in multiple tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rebelk0de
Starting Member

Malaysia
4 Posts

Posted - 02/07/2008 :  16:47:11  Show Profile  Visit rebelk0de's Homepage  Click to see rebelk0de's MSN Messenger address  Reply with Quote
Hi,

I am rather new to SQL 2005 and I am currently facing a problem when I attempt to sum the income and expenses which requires joining 2 tables. My sql string that is currently having a problem is listed below:

I USED THIS STATEMENT:

SELECT TblIncome.Recieve, SUM(TblIncome.Amount) AS TotalIncome, SUM(TblExpenses.Amount) AS TotalExpenses
FROM tblincome,tblexoenses
WHERE tblincome.recieve=tblexpenses.paymentto
GROUP BY TblIncome.Recieve

AND I ALSO USED THIS STATEMENT:

SELECT TblIncome.Recieve, SUM(TblIncome.Amount) AS TotalIncome, SUM(TblExpenses.Amount) AS TotalExpenses
FROM TblIncome INNER JOIN
TblExpenses ON TblIncome.Recieve = TblExpenses.paymentTo
GROUP BY TblIncome.Recieve


The problem that occurs is it sums and multiplies the number of records in the second table. For example,

TblIncome:

Recieve Amount
John 5
John 2
David 1


TblExpenses:

PaymentTo Amount
John 3
David 2

Currently, it is showing me as follows:

Recieve TotalIncome TotalExpenses
John 7 6
David 1 2

The problem is in total expenses, it multiplies the number of records in TblIncome.

Also, if TblExpenses has more than 1 record, it multiplies the total income with the number of records.

Does anyone know how to solve this problem? Thanks

--
http://blog.slickw0rm.net

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/07/2008 :  23:37:02  Show Profile  Reply with Quote
Hi rebelk0de,
First of all i really appreciate the way you posted. Even after being your first post,you really showed how one should post to get proper clarification of problem. Thanks for providing the table info,sample data and clearly specifying your requirement.

Now the problem, the result you receive is as expected as join will match all possible combination based on field specified. so in your case the result of join will be

Recieve Amount PaymentTo Amount
John 5 John 3
John 2 John 3
David 1 David 2

then when you take sum for John it will take sum(amount) as 5+2=7
and sum(Amount) as 3+3=6. that is what you are getting to avoid double counting from both tables you need to do this only for distinct set of data.
so:-
SELECT TblIncome.Recieve, SUM(DISTINCT TblIncome.Amount) AS TotalIncome, SUM(DISTINCT TblExpenses.Amount) AS TotalExpenses
FROM TblIncome INNER JOIN
TblExpenses ON TblIncome.Recieve = TblExpenses.paymentTo
GROUP BY TblIncome.Recieve

this will give you
Recieve    TotalIncome TotalExpenses
---------- ----------- -------------
David      1           2
John       7           3


Edited by - visakh16 on 02/07/2008 23:38:50
Go to Top of Page

rebelk0de
Starting Member

Malaysia
4 Posts

Posted - 02/08/2008 :  17:35:43  Show Profile  Visit rebelk0de's Homepage  Click to see rebelk0de's MSN Messenger address  Reply with Quote
Firstly, thanks Visakh16 for the prompt reply. I really appreciate it. I tried to test the statement that you had supplied in your response but somehow it is not working accurately. The problem that I face when I use your statement is that it is not showing the accurate data that we need.

Anyways, I managed to solve the problem using the following SQL statement.

SELECT TblStudent.Name, tblincome.amount AS tblIncome, tblexpenses.amount AS tblexpenses
FROM TblStudent INNER JOIN
(SELECT Recive, SUM(Amount) AS amount
FROM TblIncome AS TblIncome_1
GROUP BY Recive) AS tblincome ON TblStudent.Name = tblincome.Recive INNER JOIN
(SELECT paymentTo, SUM(Amount) AS amount
FROM TblExpenses AS TblExpenses_1
GROUP BY paymentTo) AS tblexpenses ON TblStudent.Name = tblexpenses.paymentTo

Now, by using this statement, it is displaying the correct data.

However, in order to fill the requirements of the system im working on, my question is, how would it be possible for me to display the same data using a specific data (FROM.... TO)

FOR EXAMPLE

The problem is when I want to show the date, I have to group it. And when I group it, it shows me duplicate data. Please let me know if you need further clarification on the example.

Thanks once again mate,

Cheers.


--
http://blog.slickw0rm.net
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/08/2008 :  23:21:25  Show Profile  Reply with Quote
Hi,Can you provide some sample data for your example?
Also you had not specified the TblStudent in your first post. If you can give some sample data from it too then that will be great.

Edited by - visakh16 on 02/08/2008 23:23:41
Go to Top of Page

rabiaah
Starting Member

5 Posts

Posted - 07/03/2008 :  01:54:05  Show Profile  Visit rabiaah's Homepage  Reply with Quote
Dear Guys,

I have the same problem with the following statement:

SELECT Contact.ContactIDE, Contact.ContactName, tblInCash.CashTotal AS tblInCash, tblInInvoice.InvoiceTotal AS tblInInvoice
FROM Contact INNER JOIN
(SELECT ContactIDE,SUM(CashTotal) AS cashres
FROM tblInCash AS CashTable GROUP BY ContactIDE) AS tblInCash ON Contact.ContactIDE=tblInCash.ContactIDE INNER JOIN
(SELECT ContactIDE,SUM(InvoiceTotal) as invoiceres
FROM tblInInvoice AS InvoiceTable
GROUP BY ContactIDE) AS tblInInvoice ON Contact.tblContactIDE=tblInInvoice.ContactIDE

Now, My Quistion as follow:

I have 5 Tables,
1- For the contact details with ContactIDE
2- Cash Table also with ContactIDE
3- Checks Table also with ContactIDE
4- Credit Table also with ContactIDE
5- Invoice Table also with ContactIDE

I tried the statement between three tables: Contact & tblInCash & tblInInvoice.

I need from those tables to Calculate the sum of the Incoming as Cash and the Outgoing in the Invoice then if the SUM of tblInCash smaller than the SUM of tblInInvoice then I will display the Contact details.

As a one sentense:
I need to display the Contacts that have to pay me.

So, I need a help here to try fix what's wrong in this SQL select statement.

---------------------------------------------------------------
Important Note: I'm using VB6.0 with MDB File for this example.


Regards...

Rabia :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/03/2008 :  02:15:06  Show Profile  Reply with Quote
I dont think there's anything wrong with your SQL staement. based on your requirement you need only a filter to get your desired output

SELECT Contact.ContactIDE, Contact.ContactName, tblInCash.CashTotal AS tblInCash, tblInInvoice.InvoiceTotal AS tblInInvoice
FROM Contact INNER JOIN
(SELECT ContactIDE,SUM(CashTotal) AS cashres
FROM tblInCash AS CashTable GROUP BY ContactIDE) AS tblInCash ON Contact.ContactIDE=tblInCash.ContactIDE INNER JOIN
(SELECT ContactIDE,SUM(InvoiceTotal) as invoiceres
FROM tblInInvoice AS InvoiceTable
GROUP BY ContactIDE) AS tblInInvoice ON Contact.tblContactIDE=tblInInvoice.ContactIDE
WHERE cashres-invoiceres <0
Go to Top of Page

rabiaah
Starting Member

5 Posts

Posted - 07/03/2008 :  05:28:00  Show Profile  Visit rabiaah's Homepage  Reply with Quote
Dear visakh16,

I'm trying to use this statement in VB6 with an MDB file, but I'm getting the following Error:

Syntax error (missing operator) in query expression ...


Regards...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/03/2008 :  05:44:58  Show Profile  Reply with Quote
MDB file? so are you using access. then please post in access forum.
Go to Top of Page

rabiaah
Starting Member

5 Posts

Posted - 07/03/2008 :  07:34:55  Show Profile  Visit rabiaah's Homepage  Reply with Quote
Dear visakh16,

Can please check my SQL Statement and try helping me to find a solution.



Best Regards...
Go to Top of Page

abhinavranjan19
Starting Member

India
1 Posts

Posted - 08/28/2012 :  05:12:55  Show Profile  Reply with Quote
Here is solution for you: http://coderskey.blogspot.in/2012/08/sql-queryprocedure-to-select-sum-of.html
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 08/28/2012 :  05:34:26  Show Profile  Reply with Quote
quote:
Originally posted by abhinavranjan19

Here is solution for you: http://coderskey.blogspot.in/2012/08/sql-queryprocedure-to-select-sum-of.html



You are 4 years late.
And why do you need the extra temp table in between ?
Also, why is the need to convert to int before sum it ?


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000