How to sum 2 tables Field - SQL Server Forums

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

How to sum 2 tables Field

Author

Topic

R.Prabu
Starting Member

33 Posts

Posted - 2008-04-07 : 14:03:33

i have two tables

Table 1 - AccountRent

Fields are

RentAmount RentYear
100 2004
200 2004
300 2004
400 2005
500 2006

Table 2 - Insurance

Fields are

InsuranceAmount ReceivedYear
100 2002
200 2002
300 2003
400 2003
500 2005
600 2005

I Need to sum these two tables 'Amount' based on the Year The Result for

IncomeAmount Year
300 2002
700 2003
600 2004
1500 2005
500 2006

Regards,
Prabu R

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-07 : 14:15:01

[code]
declare @AccountRent table (RentAmount int, RentYear int)
insert into @AccountRent
select 100, 2004 union all
select 200, 2004 union all
select 300, 2004 union all
select 400, 2005 union all
select 500, 2006

declare @Insurance table (InsuranceAmount int, ReceivedYear int)
insert into @Insurance
select 100, 2002 union all
select 200, 2002 union all
select 300, 2003 union all
select 400, 2003 union all
select 500, 2005 union all
select 600, 2005

select Yr, sum(Amt) as Amt from (
select RentAmount as Amt, RentYear as Yr from @AccountRent
union all
select InsuranceAmount as Amt, ReceivedYear as Yr from @Insurance
) a
group by Yr

[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

R.Prabu
Starting Member

33 Posts

Posted - 2008-04-07 : 14:56:01

thanks for your replay,

Here i changed the query but its showing error, just i changed the Table name

i don't want the insert statement

Error: "Invalid object name 'Tbl_AccountRent'"

select Yr, sum(Amt) as Amt from (
select RentAmount as Amt, RentYear as Yr from Tbl_AccountRent
union all
select InsuranceAmount as Amt, ReceivedYear as Yr from Tbl_AccountInsurance
) a
group by Yr

Regards,
Prabu R

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-07 : 15:00:53

Are you in the correct database
IE does "select RentAmount as Amt, RentYear as Yr from Tbl_AccountRent "
return data if not prepend the databse and owner

select RentAmount as Amt, RentYear as Yr from DATABASE.OWNER.Tbl_AccountRent

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

R.Prabu
Starting Member

33 Posts

Posted - 2008-04-07 : 15:20:49

Yes you are right, your query executed correclty but

I applied you query in my requirement, it showing error tell me the solution my requirement is

the "propertyId" field having the three tables the two tables results should displayed based "HSSPMS_Tbl_PropertyMaster" table OwnerId='LL564547' and the two tables should displayed based on the year

It displaying Error,

select Yr, sum(Amt) as Amt from ( SELECT SUM(ISNULL(AR.PaidAmount, 0)) AS Income, AR.RentYear
FROM HSSPMS_Tbl_AccountRent AS AR GROUP BY AR.RentYear
Union All
SELECT SUM(ISNULL(AI.ActualCost, 0)) AS Income , AI.ReceivedYear
FROM HSSPMS_Tbl_AccountInsurance As AI GROUP BY AI.ReceivedYear)
WHERE AR.PropertyId In (SELECT PropertyId FROM HSSPMS_Tbl_PropertyMaster
WHERE OwnerId='LL564547') GROUP BY AR.RentYear

Regards,
Prabu R

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-07 : 15:40:13

Next time post what you are looking for not part of the problem.



select Yr, sum(Amt) as Amt from ( 
	SELECT SUM(ISNULL(AR.PaidAmount, 0)) AS Amt, AR.RentYear as yr
	FROM HSSPMS_Tbl_AccountRent AS AR 
	WHERE AR.PropertyId In (SELECT PropertyId FROM HSSPMS_Tbl_PropertyMaster WHERE OwnerId='LL564547')
	GROUP BY AR.RentYear 
	Union All
	SELECT SUM(ISNULL(AI.ActualCost, 0)) AS Amt, AI.ReceivedYear as yr
	FROM HSSPMS_Tbl_AccountInsurance As AI 
	GROUP BY AI.ReceivedYear
) A
GROUP BY A.Yr

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

R.Prabu
Starting Member

33 Posts

Posted - 2008-04-07 : 16:00:40

Hi,

This query is executed but the Result is Wrong. give me the solution

Regards,
Prabu R

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-07 : 16:09:23

What makes you think the result it wrong??

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

R.Prabu
Starting Member

33 Posts

Posted - 2008-04-07 : 16:20:04

WHERE AR.PropertyId In (SELECT PropertyId FROM HSSPMS_Tbl_PropertyMaster WHERE OwnerId='LL564547')
GROUP BY AR.RentYear

This Condition Is not applied for below select statement. The above condition should apply for both select statement and also it display the all the amount and year

SELECT SUM(ISNULL(AI.ActualCost, 0)) AS Amt, AI.ReceivedYear as yr
FROM HSSPMS_Tbl_AccountInsurance As AI
GROUP BY AI.ReceivedYear

Regards,
Prabu R

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-07 : 16:22:22

Well since you will not post your table structure, sample data and expected output, I guessed based on your previous query.

Since you know what is wrong now correct it.
Or post your table structure sample data and the expected output.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

R.Prabu
Starting Member

33 Posts

Posted - 2008-04-07 : 16:40:41

I have 3 Tables

Table 1 - AccountRent

Fields are

RentAmount RentYear PropertyId
100 2004 PT0101
200 2004 PT0102
300 2004 PT0103
400 2005 PT0104
500 2006 PT0105

Table 2 - Insurance

Fields are

InsuranceAmount ReceivedYear PropertyId
100 2002 PT0101
200 2002 PT0102
300 2003 PT0103
400 2003 PT0104
500 2005 PT0105
600 2005 PT0106

Table3 - Master

OwnerId PropertyId
LL564547 PT0101
LL564547 PT0102
LL564547 PT0103
LL564547 PT0104
LL564547 PT0105
LL564547 PT0106
LL564548 PT0107
LL564548 PT0108
LL564548 PT0109
LL564548 PT01010
.
.
.

The Table 1 and 2 results display based on the table3 OwnerId='LL564547' Which all propertyId matches the table3 OwnerId. Now i want Sum Table1 RentAmount and Table 2 InsuranceAmount These two Amount should group by year. The Table3 Condition OwnerId='LL564547' Should applicable for Tabl1 and Table 2

Expected Result

IncomeAmount Year
300 2002
700 2003
600 2004
1500 2005
500 2006

I think now you are understand my requirement

Regards,
Prabu R

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-07 : 17:09:18

First This is not your table structure you have table names like HSSPMS_Tbl_AccountRent etc.

Second please post your data as I have here.
That way we do not have to spend time getting it set up so we can insert it.


declare @AccountRent table (RentAmount int, RentYear int, PropertyId varchar(20))
insert into @AccountRent 
select 100, 2004, 'PT0101' union all
select 200, 2004, 'PT0102' union all
select 300, 2004, 'PT0103' union all
select 400, 2005, 'PT0104' union all
select 500, 2006, 'PT0105'

declare @Insurance table (InsuranceAmount int, ReceivedYear int, PropertyId varchar(20))
insert into @Insurance 
select 100, 2002, 'PT0101' union all
select 200, 2002, 'PT0102' union all
select 300, 2003, 'PT0103' union all
select 400, 2003, 'PT0104' union all
select 500, 2005, 'PT0105 ' union all
select 600, 2005, 'PT0106' 

declare @Master table (OwnerId varchar(20), PropertyId varchar(20))

insert into @Master
select 'LL564547', 'PT0101' union all
select 'LL564547', 'PT0102' union all
select 'LL564547', 'PT0103' union all
select 'LL564547', 'PT0104' union all
select 'LL564547', 'PT0105' union all
select 'LL564547', 'PT0106' union all
select 'LL564548', 'PT0107' union all
select 'LL564548', 'PT0108' union all
select 'LL564548', 'PT0109' union all
select 'LL564548', 'PT01010' 

-- Table 1 - AccountRent
-- RentAmount RentYear PropertyId
-- 
-- Table 2 - Insurance
-- InsuranceAmount ReceivedYear PropertyId
-- 
-- Table 3 - Master
-- OwnerId PropertyId

select sum(amt) as IncomeAmount, yr as 'Year'
from (
	select IsNull(a.RentAmount,0) as amt, a.RentYear as yr 
		from @AccountRent a join @Master b on a.PropertyId = b.PropertyId AND b. OwnerID = 'LL564547'
	union all
	select IsNull(a.InsuranceAmount,0) as amt, a.ReceivedYear as yr
		from @Insurance a join @master b on a.PropertyId = b.PropertyId AND b. OwnerID = 'LL564547'
) a
group by yr

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

R.Prabu
Starting Member

33 Posts

Posted - 2008-04-07 : 17:59:52

Thanks You,

I got the Expected result, its Working Fine, I gave all the table structure, just i changed the table name for easy to understand
Thank for spending time to here.

Regards,
Prabu R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources