different in same column - 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

different in same column

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-11-11 : 23:56:28

how to count different in same row by ID?

ID amount detail
1 50.00 a
1 20.00 b
2 100.00 a
2 80.00 b

expected result:

ID amount
1 30.00
2 20.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-12 : 00:06:49

a - b ?


select ID, sum(case when detail = 'a' then amount else -amount end)
from   yourtable
group by ID

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-13 : 13:21:35

Here is a slight variation on Khtan's that doesn't use the Desc column (notice how the sample data is in a consumable format):

DECLARE @Foo TABLE (ID INT, amount MONEY, detail CHAR(1))

INSERT @Foo VALUES
(1, $50.00, 'a'),
(1, $20.00, 'b'),
(2, $100.00, 'a'),
(2, $80.00, 'b')


;WITH Cte AS
(
	SELECT
		ID,
		Amount,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RowNum
	FROM
		@Foo AS F
)


SELECT
	ID,
	ABS(SUM(CASE WHEN RowNum = 1 THEN Amount ELSE (-Amount) END)) AS Amount
FROM
	Cte
GROUP BY
	ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 02:20:16

quote:

Originally posted by Lamprey

Here is a slight variation on Khtan's that doesn't use the Desc column (notice how the sample data is in a consumable format):

DECLARE @Foo TABLE (ID INT, amount MONEY, detail CHAR(1))

INSERT @Foo VALUES
(1, $50.00, 'a'),
(1, $20.00, 'b'),
(2, $100.00, 'a'),
(2, $80.00, 'b')


;WITH Cte AS
(
	SELECT
		ID,
		Amount,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RowNum
	FROM
		@Foo AS F
)


SELECT
	ID,
	ABS(SUM(CASE WHEN RowNum = 1 THEN Amount ELSE (-Amount) END)) AS Amount
FROM
	Cte
GROUP BY
	ID

This wont work if order of records are reversed for ID ie detail = 'b' first followed by 'a'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-14 : 12:57:15

quote:
Originally posted by visakh16

quote:
Originally posted by Lamprey

Here is a slight variation on Khtan's that doesn't use the Desc column (notice how the sample data is in a consumable format):
DECLARE @Foo TABLE (ID INT, amount MONEY, detail CHAR(1))

INSERT @Foo VALUES
(1, $50.00, 'a'),
(1, $20.00, 'b'),
(2, $100.00, 'a'),
(2, $80.00, 'b')

;WITH Cte AS
(
SELECT
ID,
Amount,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RowNum
FROM
@Foo AS F
)

SELECT
ID,
ABS(SUM(CASE WHEN RowNum = 1 THEN Amount ELSE (-Amount) END)) AS Amount
FROM
Cte
GROUP BY
ID

This wont work if order of records are reversed for ID ie detail = 'b' first followed by 'a'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Won't work how?

Plus, the OP, didn't seem to case about the Desc column

quote:
how to count different in same row by ID?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 13:32:29

Ah...sorry didnt notice the ABS function.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources