Filters out Groups if contains a certain Value

Transact-SQL
1

I have gotten this to work but I would like to know if there is a better way of writing this. Basically, I have a result set that returns data in groups. I would like to exclude the groups where it contains a certain value. For this example, I want to exclude all groups that has a value of '2'. I have gotten it to work but wonder if there is a way of using HAVING to filter out a group or a better way.

DROP TABLE IF EXISTS #TEMPTest
SELECT * INTO #TEMPTest FROM
(
SELECT 'Group A' AS 'Group_Name', '1'	AS 'This_Value'
UNION
SELECT 'Group A' AS 'Group_Name', '2'	AS 'This_Value'
UNION
SELECT 'Group A' AS 'Group_Name', '3'	AS 'This_Value'
UNION
SELECT 'Group B' AS 'Group_Name', '4'	AS 'This_Value'
UNION
SELECT 'Group B' AS 'Group_Name', '5'	AS 'This_Value'
UNION
SELECT 'Group B' AS 'Group_Name', '6'	AS 'This_Value'
) T1

SELECT * FROM #TEMPTest

--This works
SELECT * FROM #TEMPTest
WHERE Group_Name NOT IN
(
	SELECT Group_Name FROM #TEMPTest
	WHERE This_Value = '2'
	GROUP BY Group_Name
)

This did not work. Looking for a more simple form to filter out group.

SELECT Group_Name FROM #TEMPTest
GROUP BY Group_Name, This_Value
HAVING This_Value NOT IN('2')
2

I am not following the problem - you have one row with This_Value = '2' which would be a simple query excluding that row.

I am guessing that you have some other query that performs the grouping and calculates This_Value. If so...

SELECT ...
FROM ...
WHERE ...
GROUP BY {grouping columns}
HAVING SUM(This_Value) <> '2'

Or - whatever aggregate you are using to determine that value.

You could also perform the 'grouping' query in a CTE...and then use a simple WHERE:

WITH groupedData
AS (
SELECT ...
FROM ...
WHERE...
GROUP BY ...
)
SELECT ...
FROM groupedData
WHERE this_value <> 2;
3

Jeff, I would like to exclude the GROUP that contains the STRING '2' in any of its row. The field This_Value is a string value, sorry if it looks like a numeric field. Therefore, I can't use the SUM.

4

Are you saying,

Groups

  • Language: French
  • Continent: Europe

Countries:

  • France

Associations

  • France => French
  • France => Europe

When you request to Exclude any data association with Europe and also exclude France French association since France is associated with Europe?

5

Yosiasz, I have Group A and Group B in my sample data (above). So when any of those groups have a value (in this case '2'), I would like to exclude from seeing that group in the result set.

6

Sorry - this doesn't make sense. If you want to exclude the row where This_Value = '2' then just exclude it in the where clause:

WHERE this_value <> '2'

If you want to exclude the group that has a This_Value = 2

SELECT * FROM #TEMPTest t1
WHERE NOT EXISTS (SELECT * FROM #TEMPTest t2 WHERE t2.Group_Name = t1.Group_Name AND This_Value = '2')

Your question is a bit confusing - since you are asking about GROUP BY but aren't using any grouping.

7

like @jeffw8713 is saying all the group by seems unnecessary. it is distracting unless you have a reason to do that?

8

Sorry for the confusion. I was trying to achieve eliminating groups so I thought the use of Group By.

9

HAVING MAX(CASE WHEN This_Value = '2' THEN 1 ELSE 0 END) = 0

1 Like