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
  • Last Modified:

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]

Open in new window

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]

Open in new window


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
Asked:
Tech_Men
  • 5
  • 2
1 Solution
 
John_VidmarCommented:
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]

Open in new window

0
 
Tech_MenAuthor 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
 
Tech_MenAuthor Commented:
like that  :
x       y     total
100  20    80
400  100  300
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Tech_MenAuthor Commented:
your total working very good but i must have does 2 columns
0
 
Tech_MenAuthor 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
 
Tech_MenAuthor Commented:
thanks
0
 
John_VidmarCommented:
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]

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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