title: TSQL Find If All Records In A Group Have The Same Value
body: <p>I have a table that contains a field which is used for grouping and another field which holds data.  I want a good way to find any GroupColumn value where every DataColumn value contains a specific value.</p>
  
  <p><strong>Example</strong></p>
  
  <pre><code>+-------------+------------+
  | GroupColumn | DataColumn |
  +-------------+------------+
  | GroupA      | Data1      |
  | GroupA      | Data2      |
  | GroupA      | Data3      |
  | GroupB      | Data1      |&lt;---These two values are the same
  | GroupB      | Data1      |&lt;---for the same group
  | GroupC      | Data1      |
  | GroupC      | Data2      |
  | GroupC      | Data2      |
  | GroupC      | Data3      |
  +-------------+------------+
  </code></pre>
  
  <p><strong>Desired Output</strong></p>
  
  <p>Group B</p>
  
  <p>In the example above the DataColumn changes for GroupA and GroupC, but for GroupB both values in the DataColumn are the same so I would want this result returned.</p>
  
  <p><strong>Current Solution</strong></p>
  
  <p>I have 2 current solutions based around the same theme, but I feel that this is something that SQL should be able to do in a easier fashion.</p>
  
  <ol>
  <li><p>Group everything in the table, count the times GroupColumn appears and put this into a table.  Do the same, but apply a condition.  Join the 2 tables and see where the 2 counts do not match.</p>
  
  <pre><code>SELECT GROUPCOLUMN, COUNT(*) [TOTAL] INTO #ALL
  FROM #TABLE
  GROUP BY GROUPCOLUMN
  
  SELECT GROUPCOLUMN, COUNT(*) [TOTAL] INTO #SOME
  FROM #TABLE
  WHERE DATACOLUMN = 'DATA1'
  GROUP BY GROUPCOLUMN
  
  SELECT * FROM #ALL A
  INNER JOIN #SOME S ON A.GROUPCOLUMN = S.GROUPCOLUMN
  WHERE S.TOTAL = A.TOTAL
  </code></pre></li>
  <li><p>Use a SUM and a CASE to check for the specific value and count everything and check in a sub-query.</p>
  
  <pre><code>SELECT * FROM 
  (SELECT GROUPCOLUMN, SUM(CASE WHEN DATACOLUMN = 'DATA1' THEN 1 ELSE 0 END) [VALUE], COUNT(*) [TOTAL] FROM #TABLE (NOLOCK)
  GROUP BY GROUPCOLUMN) A
  WHERE A.VALUE = A.TOTAL
  </code></pre></li>
  </ol>
  
  <p>Is there a better way to do this in SQL?</p>
  
  <p>Thanks in advance.</p>
  
  <p>Ninja</p>