Using two Sum function in multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using two Sum function in multiple tables

Author

Topic

rebelk0de
Starting Member

4 Posts

Posted - 2008-02-07 : 16:47:11

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

52326 Posts

Posted - 2008-02-07 : 23:37:02

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

rebelk0de
Starting Member

4 Posts

Posted - 2008-02-08 : 17:35:43

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 23:21:25

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.

rabiaah
Starting Member

5 Posts

Posted - 2008-07-03 : 01:54:05

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 :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 02:15:06

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

rabiaah
Starting Member

5 Posts

Posted - 2008-07-03 : 05:28:00

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...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 05:44:58

MDB file? so are you using access. then please post in access forum.

rabiaah
Starting Member

5 Posts

Posted - 2008-07-03 : 07:34:55

Dear visakh16,

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

Best Regards...

abhinavranjan19
Starting Member

1 Post

Posted - 2012-08-28 : 05:12:55

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-28 : 05:34:26

quote:
Originally posted by abhinavranjan19

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

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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources