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.