Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 259

# select Sum from 2 tables

hi there
i have go get sum from 2 tables
in one table i have the total debt for each agreement this is the table :
``````SELECT [MID]
,[AID]
,[Pay_Date]
,[Pay_Sum]
,[Rem]
,[Is_Pay]
FROM [Agreements_Moves]
``````
i need the sum on Pay_Sum col for each AID

the second table that i need to run Sum on PaySum col  on the payment Table this is the table :
``````SELECT [PayRunId]
,[receipt_id]
,[MID]
,[AID]
,[PayDate]
,[PaySum]
[dbo].[Pays]
``````

i need to see what is the total from the first table then see what is the total from the second table then X-Y = balance
for each AID in the first table
thanks ....
0
Tech_Men
• 5
• 2
1 Solution

Commented:
``````SELECT	[AID]	= ISNULL(a.[AID],b.[AID])
,	[diff]	= ISNULL(a.x,0) - ISNULL(b.y,0)
FROM	(	SELECT	[AID]
,	x = SUM([Pay_Sum])
FROM	[Agreements_Moves]
GROUP
BY	[AID]
) a
FULL
JOIN
(	SELECT	[AID]
,	y = SUM([PaySum])
FROM	[dbo].[Pays]
GROUP
BY	[AID]
) b	ON	a.[AID] = b.[AID]
``````
0

Author Commented:
hi john
thanks for your answer its working but i need to see more 2 col
the total from the first table and the total from the second table as well
0

Author Commented:
like that  :
x       y     total
100  20    80
400  100  300
0

Author Commented:
your total working very good but i must have does 2 columns
0

Author Commented:
maybe this will help to does 2 tables are main table as well to the Agreements_Moves has an Agreements table
and the Pays have a main table called Receipts
0

Author Commented:
thanks
0

Commented:
``````SELECT	[AID]	= ISNULL(a.[AID],b.[AID])
,	[x]	= ISNULL(a.x,0)
,	[y]	= ISNULL(b.y,0)
,	[total]	= ISNULL(a.x,0) - ISNULL(b.y,0)
FROM	(	SELECT	[AID]
,	x = SUM([Pay_Sum])
FROM	[Agreements_Moves]
GROUP
BY	[AID]
) a
FULL
JOIN
(	SELECT	[AID]
,	y = SUM([PaySum])
FROM	[dbo].[Pays]
GROUP
BY	[AID]
) b	ON	a.[AID] = b.[AID]
``````
0

## Featured Post

• 5
• 2
Tackle projects and never again get stuck behind a technical roadblock.