Grouping in MS SQL is pretty straight forward. Today I had a question where a user needed to perform a task similar to this:
SELECT sum(fieldx) FROM mytable INNER JOIN tblx on … WHERE fieldx in (1,2,3,4,5…) GROUP BY fieldx …
But they needed to link certain entries that had nothing linking them. There were 2 solutions to this problem:
- Sticking a CASE clause in the GROUP BY clause: Group by (case when fieldx = ‘1’ then ‘2’ when fieldx = ‘3’ then ‘4’ else fieldx end) This basically joins 1 & 2, 3 & 4, and then groups the rest.
- Creating a separate table listing the id’s and the region and then join ni this table and grouping by region
The user went for that latter solution the end.