CODEWITHSUNDEEP
teradata
staove7
staove7

Reputation: 580

creating groups under specific conditions

I've the following table A (this is just sample) which presents the channel the customer make a reservation:

custNum  channelType  reservationNumber  
 1        web           1
 2        Iphone        2
 1        Android       3
 3        web           4

and I'm trying to separate between 3 groups of customers (I'd like to get their cust numbers):
1. customers who used only the web
2. customers who used only the cellular channels
3. customers who used both

under the sample above, the results should be:
1. web only (custNum=3)
2. cellular only (custNum=2)
3. web+cellular (custNum=1)

I'd tried the following for web usesr only (but I know it's wrong): 

sel custNum from A where channelType in ('web');

any help will be appreciated.

Upvotes: 0

Views: 24

Answers (1)

dnoeth
dnoeth

Reputation: 60482

You need to do some conditional aggregation like this:

select custNum,
   case 
      when max(case when channelType = 'web' then 1 else 2 end) = 1
        then 'web only'
      when max(case when channelType = 'web' then 2 else 1 end) = 1
        then 'celular only'
      else 'both'
   end
from tab
group by custNum

Upvotes: 1

Related Questions