asked on

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

ASKER CERTIFIED SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tech_Men

ASKER

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

Tech_Men

ASKER

like that :
x y total
100 20 80
400 100 300

Tech_Men

ASKER

your total working very good but i must have does 2 columns

Tech_Men

ASKER

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

Tech_Men

ASKER

thanks

John_Vidmar

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