TSQL Find If All Records In A Group Have The Same Value

Question

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.

Example

+-------------+------------+
| GroupColumn | DataColumn |
+-------------+------------+
| GroupA      | Data1      |
| GroupA      | Data2      |
| GroupA      | Data3      |
| GroupB      | Data1      |<---These two values are the same
| GroupB      | Data1      |<---for the same group
| GroupC      | Data1      |
| GroupC      | Data2      |
| GroupC      | Data2      |
| GroupC      | Data3      |
+-------------+------------+

Desired Output

Group B

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.

Current Solution

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.

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

   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
   

2. Use a SUM and a CASE to check for the specific value and count everything and check in a sub-query.

   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
   

Is there a better way to do this in SQL?

Thanks in advance.

Ninja

Answer 1

You are looking for HAVING clause

SELECT GROUPCOLUMN
FROM   #TABLE (NOLOCK)
GROUP  BY GROUPCOLUMN
HAVING Count(*) = Count(case when DATACOLUMN = 'DATA1' then 1 end) 

Answer 2

It sounds like you are looking for each group that has a single distinct value in DATACOLUMN:

SELECT GROUPCOLUMN
FROM #TABLE
GROUP BY GROUPCOLUMN
HAVING COUNT(DISTINCT DATACOLUMN) = 1

Note that COUNT(DISTINCT ...) does not count NULL as a distinct value.

Answer 3

You should be able to compare COUNT(*) with COUNT(DISTINCT DATACOLUMN) to do this. Like this:

SELECT GROUPCOLUMN
FROM #TABLE
GROUP BY GROUPCOLUMN
HAVING Count(*) = Count(DISTINCT DATACOLUMN)