Grouping an MS SQL WHERE Clause

15 Apr

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:

  1. 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.
  2. 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.