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 2012 Forums
 Transact-SQL (2012)
 weird group by

Author  Topic 

xholax
Starting Member

12 Posts

Posted - 2013-02-13 : 17:43:07
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-13 : 19:35:41
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

12 Posts

Posted - 2013-02-18 : 10:40:52
thanks man it worked
Go to Top of Page
   

- Advertisement -