Is there a way to collect the data and inspect in one pass using groupby function

Question

Sample Data of table_1

Have this Query that returns select customer, SUM(CASE WHEN activity IN ( 'a','b') THEN 1 ELSE 0 END) AS num_activity_a_or_b from table_1 group by customer

Results:

Want to extend this to return one more column if for a given code say X1 if the Activity is "a" and "c" then return num_of_a_and_c_activity.

A bit stuck how to collect and inpect the code and activities in one pass. can we combine windowing function to achieve this.

Please advise and help

Answer 1

UPDATE: based on the updated results, maybe the below query is what you need So what i assume is that you need both a and c as well x1 . So I count distinct activities which are a and c and then do integer division by 2. if only a is present then count distinct =1 but 1/2 =0 in integer division. It is only 1 when both a and c are present.

select 
 customer, 
  SUM(CASE WHEN activity IN ( 'a','b') 
     THEN 1 
     ELSE 0 
   END) AS num_activity_a_or_b,
   COUNT(DISTINCT CASE WHEN code IN ('x1') AND activity IN ( 'a','c') 
     THEN activity 
      ELSE NULL 
   END)/2 AS num_activity_a_and_c
from table_1 
group by customer

Maybe your query can be

select 
 customer, 
  SUM(CASE WHEN activity IN ( 'a','b') 
     THEN 1 
     ELSE 0 
   END) AS num_activity_a_or_b,
   SUM(CASE WHEN code IN ('x1') AND activity IN ( 'a','c') 
     THEN 1 
      ELSE 0 
   END) AS num_activity_a_or_c
from table_1 
group by customer