# Issue

This Content is from Stack Overflow. Question asked by template

How can I have a condition on the value within a group. Based on the table formed below I would like to check when grouping on groupb that groupa contains the value GA3 or GA4 if it contains the value GA3 than I want to output the value to a certain column A if it contains GA3 but also GA4 than in another column.

``````WITH test AS (
SELECT 10 AS a, 20 AS b, 30 AS c,'GA1' AS groupa,'GB2' AS groupb FROM dual UNION ALL
SELECT 11 AS a, 21 AS b, 31 AS c,'GA2' AS groupa,'GB2' AS groupb FROM dual UNION ALL
SELECT 12 AS a, 22 AS b, 32 AS c,'GA2' AS groupa,'GB1' AS groupb FROM dual UNION ALL
SELECT 12 AS a, 22 AS b, 32 AS c,'GA3' AS groupa,'GB1' AS groupb FROM dual UNION ALL
SELECT 14 AS a, 24 AS b, 34 AS c,'GA4' AS groupa,'GB1' AS groupb FROM dual UNION ALL
SELECT 13 AS a, 23 AS b, 33 AS c,'GA1' AS groupa,'GB1' AS groupb FROM dual
)
SELECT
groupb,
SUM(CASE WHEN (groupa IN ('GA3') AND groupa NOT IN ('GA4')) THEN 1 ELSE 0 END) AS ga1test,
SUM(CASE WHEN (groupa IN ('GA4')) THEN 1 ELSE 0 END) AS ga2test
FROM test
GROUP BY groupb
``````

So above I have ga1test and ga2test. ga1test should contain the value if the group has a value GA3 but not GA4; ga2test should have a value if there is a value GA4. In the current select ga1test should be empty as there is a GA4 value (but it’s not); ga2test should contain a value.

# Solution

You may use the `EXISTS` operator as the following:

``````SELECT
T.groupb,
SUM(CASE WHEN (T.groupa = 'GA3' AND NOT EXISTS(SELECT 1 FROM test D WHERE D.groupb=T.groupb And D.groupa = 'GA4')) THEN 1 ELSE 0 END) AS ga1test,
SUM(CASE WHEN (T.groupa = 'GA4') THEN 1 ELSE 0 END) AS ga2test
FROM test T
GROUP BY T.groupb
``````

See a demo.

Your condition `WHEN (groupa = 'GA3' AND groupa <> 'GA4')` will be always true when groupa =’GA3′, SQL query fetches the results row by row, so if groupa =’GA3′ of course it will not be =’GA4′.

The `EXISTS` operator with the subquery works like a for loop that scans the table to check if there is a value of groupa that is =’GA4′.

``` This Question was asked in  StackOverflow by  template and Answered by Ahmed It is licensed under the terms of
CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.```