SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 different in same column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

410 Posts

Posted - 11/11/2013 :  23:56:28  Show Profile  Reply with Quote
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)

Singapore
17635 Posts

Posted - 11/12/2013 :  00:06:49  Show Profile  Reply with Quote
a - b ?

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



KH
Time is always against us

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/13/2013 :  13:21:35  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/14/2013 :  02:20:16  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/14/2013 :  12:57:15  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/14/2013 :  13:32:29  Show Profile  Reply with Quote
Ah...sorry didnt notice the ABS function.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000