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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 weird group by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xholax
Starting Member

Argentina
12 Posts

Posted - 02/13/2013 :  17:43:07  Show Profile  Reply with Quote
Hello, I hope you can help me


as you can see in the picture above, i need to group by plant -plant2
doesnt matter if I have a - b or b-a to count a row for a group

Lamprey
Flowing Fount of Yak Knowledge

4604 Posts

Posted - 02/13/2013 :  19:35:41  Show Profile  Reply with Quote
Here are a couple of ways (PS it's easier if you post your data in a consumable format):
--Sample Data
DECLARE @Foo TABLE (Plant CHAR(1), Plant2 CHAR(1))

INSERT @Foo
VALUES
('a', 'b'),
('b', 'a'),
('a', 'd'),
('s', 'y'),
('a', 'b')

-- Using MIN/MAX with VALUES clause
SELECT
	P1 AS Plant,
	P2 AS Plant2,
	COUNT(*) AS [Sum]
FROM
	(
		SELECT
			(SELECT MIN(P) FROM (VALUES (Plant), (Plant2)) AS Foo(P)) AS P1,
			(SELECT MAX(P) FROM (VALUES (Plant), (Plant2)) AS Foo(P)) AS P2
		FROM
			@Foo AS F
	) AS T
GROUP BY
	P1, P2

--Using Case expression
SELECT 
	CASE WHEN Plant <= Plant2 THEN Plant ELSE Plant2 END AS Plant,
	CASE WHEN Plant > Plant2 THEN Plant ELSE Plant2 END AS Plant2,
	COUNT(*) AS [Sum]
FROM @Foo
GROUP BY
	CASE WHEN Plant <= Plant2 THEN Plant ELSE Plant2 END,
	CASE WHEN Plant > Plant2 THEN Plant ELSE Plant2 END
Go to Top of Page

xholax
Starting Member

Argentina
12 Posts

Posted - 02/18/2013 :  10:40:52  Show Profile  Reply with Quote
thanks man it worked
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.05 seconds. Powered By: Snitz Forums 2000