SQL select in group when group contains a certain value but not another

template 0
Viewed 31

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 = 'GA3' AND groupa <> 'GA4') THEN 1 ELSE 0 END) AS X,
  SUM(CASE WHEN groupa  = 'GA4' AND groupa  = 'GA3' THEN 1 ELSE 0 END) AS Y
FROM test
GROUP BY groupb

So above I have output-column X and Y. X should contain the value if the group has a value GA3 but not GA4; Y should have a value if there is a value GA4. In other words if the group (group by groupb) contains only GA3 than the value needs to be placed in output column A, if the group contains GA3 AND GA4 than it needs to be placed in column B.

oracle sql
1 Answers

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

userGpjR0
Related