Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

4614 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  
 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