problem in SUM - 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

SQL Server 2005 Forums

Transact-SQL (2005)

problem in SUM

Author

Topic

vijaynet
Starting Member

7 Posts

Posted - 2008-08-13 : 19:06:47

I have two tables.

Table name - Profit

Id PropertyId Profit
---- ------- ------------------
1 P1 1000.00
2 P1 2000.00

Table name - Loss
Id PropertyId Loss
----------- ---------------- ----------------------------------
1 P1 1000.00

My query

select P.PropertyId,L.PropertyId,isnull(Sum(P.Profit),0) As 'Profit', isnull(sum(L.Loss),0) As 'Loss'
from Profit P inner join Loss L On P.PropertyId=L.PropertyId
where P.PropertyId='P1'
Group by P.PropertyId,L.PropertyId

Result

PropertyId PropertyId Profit Loss
------ ----------- ------------ ---------------------
P1 P1 3000.00 2000.00

But i need the below result

PropertyId PropertyId Profit Loss
--------- -------- ------------- --------------------
P1 P1 3000.00 1000.00

How can i get this result.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 19:11:34

Try this:


select P.PropertyId, L.PropertyId, dt.Profit, L.Loss
from Profit P 
inner join Loss L 
	on P.PropertyId=L.PropertyId
inner join
(
	select PropertyId, isnull(Sum(Profit),0) As 'Profit'
	from Profit 
	where PropertyId='P1'
	Group by PropertyId
) dt
on P.PropertyId = dt.PropertyId

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 19:12:58

Actually, this can be simplified to:


select P.PropertyId, L.PropertyId, P.Profit, L.Loss
from Loss L 
inner join
(
	select PropertyId, isnull(Sum(Profit),0) As 'Profit'
	from Profit 
	where PropertyId='P1'
	Group by PropertyId
) P
on L.PropertyId = P.PropertyId

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vijaynet
Starting Member

7 Posts

Posted - 2008-08-13 : 19:22:25

thank u for ur reply

If i included the one more record to the both tables. The result was not group.
Plz give me a solution.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 19:30:57

I don't understand your last reply. Please show us what you mean.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vijaynet
Starting Member

7 Posts

Posted - 2008-08-13 : 19:43:00

I have included the one more record to Profit and Loss table

Table name - Profit

Id PropertyId Profit
---- ------- ------------------
1 P1 1000.00
2 P1 2000.00
3 P1 3000.00

Table name - Loss
Id PropertyId Loss
----------- ---------------- ----------------------------------
1 P1 1000.00
1 P1 2000.00

Result for ur query

PropertyId PropertyId Profit Loss
------ ----------- ------------ ---------------------
P1 P1 6000.00 1000.00
P1 P1 6000.00 2000.00

So, the result is not group.
I need the below result.

PropertyId PropertyId Profit Loss
------ ----------- ------------ ---------------------
P1 P1 6000.00 3000.00

Table records will grow in future.It must shows with group.

vijaynet
Starting Member

7 Posts

Posted - 2008-08-13 : 20:00:59

Dear tKizer,
Can u understand my probs.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 22:59:19

Your original query should do that already. Are you not telling the whole story?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-13 : 23:58:02

[code]DECLARE @Profit TABLE
(
Id int,
PropertyId varchar(2),
Profit decimal(10,2)
)
INSERT INTO @Profit
SELECT 1, 'P1', 1000.00 UNION ALL
SELECT 2, 'P1', 2000.00 UNION ALL
SELECT 3, 'P1', 3000.00

DECLARE @Loss TABLE
(
Id int,
PropertyId varchar(2),
Loss decimal(10,2)
)
INSERT INTO @Loss
SELECT 1, 'P1', 1000.00 UNION ALL
SELECT 1, 'P1', 2000.00

SELECT p.PropertyId, p.Profit, l.Loss
FROM
(
SELECT PropertyId, Profit = SUM(Profit)
FROM @Profit
GROUP BY PropertyId
) p
INNER JOIN
(
SELECT PropertyId, Loss = SUM(Loss)
FROM @Loss
GROUP BY PropertyId
) l ON p.PropertyId = l.PropertyId

/*
PropertyId Profit Loss
---------- ------------ ------------
P1 6000.00 3000.00

(1 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

vijaynet
Starting Member

7 Posts

Posted - 2008-08-14 : 13:08:33

Thank u very much Khtan,
Its working well.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 17:18:28

There is a slight problem with all suggestions above.
Do they show the propertyid's where there are only profit and no loss?
Or when there is a propertyid with only loss and no profit?

Try this code

DECLARE	@Profit TABLE (ID INT, PropertyID CHAR(2), Profit MONEY)

INSERT	@Profit
SELECT	1, 'P1', 1000.00 UNION ALL
SELECT	2, 'P1', 2000.00 UNION ALL
SELECT	3, 'P1', 3000.00 UNION ALL
SELECT	4, 'P2', 2000.00

DECLARE	@Loss TABLE (ID INT, PropertyID CHAR(2), Loss MONEY)

INSERT	@Loss
SELECT	1, 'P1', 1000.00 UNION ALL
SELECT	2, 'P1', 2000.00 UNION ALL
SELECT	3, 'P3',  500.00

-- Peso
SELECT		PropertyID,
		SUM(Profit) AS Profit,
		SUM(Loss) AS Loss
FROM		(
			SELECT	PropertyID,
				Profit,
				0 AS Loss
			FROM	@Profit

			UNION ALL

			SELECT	PropertyID,
				0,
				Loss
			FROM	@Loss
		) AS d
GROUP BY	PropertyID
ORDER BY	PropertyID

E 12°55'05.25"
N 56°04'39.16"

vijaynet
Starting Member

7 Posts

Posted - 2008-08-14 : 17:43:35

No peso,
Its working well.
I have one more doubt

UniqueCode Labour Parts Asset
---------- ------ ----- ------
1 50.00 0 0
1 0 50.00 0
1 0 0 50.00

I need the below result

UniqueCode Labour Parts Asset
---------- ------ ----- ------
1 50.00 50.00 50.00

Help me.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 17:48:58

quote:
Originally posted by vijaynet

No peso,
Its working well.

What?
See this sample code

DECLARE	@Profit TABLE (ID INT, PropertyID CHAR(2), Profit MONEY)

INSERT	@Profit
SELECT	1, 'P1', 1000.00 UNION ALL
SELECT	2, 'P1', 2000.00 UNION ALL
SELECT	3, 'P1', 3000.00 UNION ALL
SELECT	4, 'P2', 2000.00

DECLARE	@Loss TABLE (ID INT, PropertyID CHAR(2), Loss MONEY)

INSERT	@Loss
SELECT	1, 'P1', 1000.00 UNION ALL
SELECT	2, 'P1', 2000.00 UNION ALL
SELECT	3, 'P3',  500.00

-- Peso
SELECT		PropertyID,
		SUM(Profit) AS Profit,
		SUM(Loss) AS Loss
FROM		(
			SELECT	PropertyID,
				Profit,
				0 AS Loss
			FROM	@Profit

			UNION ALL

			SELECT	PropertyID,
				0,
				Loss
			FROM	@Loss
		) AS d
GROUP BY	PropertyID
ORDER BY	PropertyID

-- Other
SELECT		p.PropertyId,
		p.Profit,
		l.Loss
FROM		(
			SELECT		PropertyId,
					SUM(Profit) AS Profit
			FROM		@Profit
			GROUP BY	PropertyId
		) AS p
INNER JOIN	(
			SELECT		PropertyId,
					SUM(Loss) AS Loss
			FROM		@Loss
			GROUP BY	PropertyId
		) as l ON l.PropertyId = p.PropertyId

My suggestion returns

PropertyID	Profit	Loss
P1		6000,00	3000,00
P2		2000,00	   0,00
P3		   0,00	 500,00

Whereas other code returns

PropertyId	Profit	Loss
P1		6000,00	3000,00

What happened to PropertyID P2 and P3?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 17:50:03

quote:
Originally posted by vijaynet

I need the below result

UniqueCode Labour Parts Asset
---------- ------ ----- ------
1 50.00 50.00 50.00

Help me.....

I have already showed you the proper way to do this.
Copy and paste my suggestion and alter as needed.

E 12°55'05.25"
N 56°04'39.16"

vijaynet
Starting Member

7 Posts

Posted - 2008-08-14 : 18:06:41

Thanks peso...
I need one more help from u.

my table name - tblPcode
Pcode Parts Asset
1         50.00    0
1         0        100.00

I need below result.

Pcode Parts    Asset
1       50.00     100.00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 18:13:57

See my first and only suggestion.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 22:47:12

quote:
Originally posted by vijaynet

Thanks peso...
I need one more help from u.

my table name - tblPcode
Pcode Parts Asset
1         50.00    0
1         0        100.00

I need below result.

Pcode Parts    Asset
1       50.00     100.00

GROUP BY Pcode and take the SUM() of other fields

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-15 : 01:35:33

Peso,

Could you help me with this:


My table name - tblPcode
Pcode   Parts   Asset
1         52.55    0
1         0        666.33

I need below result.

Pcode Parts    Asset
1       52.55     666.33

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 02:33:30

I know Tara, I know...

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources