Which join to be used in sql to get sum of a fields

Question

0.00/5 (No votes)

See more:

I had table a table Expensemaster with data like below

ExpenseID   ExpAmount   ExpPK   ExpDate
1            20            23  17-Jul-2014
2            40            34  17-Jul-2014
3            45            23  17-Jul-2014
4            69            34  22-Jul-2014
5            45            54  22-Jul-2014
6            67            63  21-Jul-2014


IncomeID    IncomeAmount    IncomPk Income Date
34           20             24       17-Jul-2014
36           40             24       16-Jul-2014
45           45             234      17-Jul-2014
46           69             214      26-Jul-2014
51           45             34       27-Jul-2014
57           67             34       21-Jul-2014

What I want is a query to show the Date (it may be Expdate or incomeDate) sum of expense amount if any and sum of Income Amount if any angainst the Date

means for all the dates in both table we should get the sum of income and expense of that day

I had tried the Outer join but failed as we have to consider both the table date can anyone suggest a better query or option

Posted 23-Jul-14 5:27am

SREENATH GANGA

Updated 23-Jul-14 5:28am

v2

Add a Solution

Comments

CHill60 23-Jul-14 11:30am

How are the tables linked? Only on the date?

SREENATH GANGA 23-Jul-14 11:32am

No only option is using the Date itself. We want to show the Expense and Income of each date
there is relationship between theese tables

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)

Maciej Los · Accepted Answer · 2014-07-23T05:37:00

Try this:

SQL

SELECT SUM(t1.ExpAmount) AS ExpAmount, SUM(t2.IncomeAmount)
FROM Expense AS t1 INNER JOIN Income AS t2 ON t1.ExpDate = t2.IncomeDate
GROUP BY t1.ExpDate
ORDER BY t1.ExpDate

Above example will return sum of amounts for equal dates.

BUT...

If you want to get sume for each day, you need to use something like this:

SQL

SELECT aDate, [E], [I]
FROM (
    SELECT ExpDate AS aDate, 'E' AS [Description], ExpAmount AS Amount
    FROM Expense
    UNION ALL
    SELECT IncomeDate AS aDate, 'I' AS [Description], IncomeAmount AS Amount
    FROM Income
) AS DT
PIVOT (SUM(Amount) FOR [Description] IN ([E], [I])) AS PT