Filtering results by reviewing multiple values for single column

Hello,

  I have a table that has data by the account for each device is associated to the account:  

 

account_id  |  model
1                    |   iPhone
1                    |   Samsung 
2                   |   iPhone
3                   |   Samsung
4                   |   iPhone
4                   |   Samsung
4                   |   LG

 

I am trying to create a query in which i collect the account ids that fall into one of the following buckets:  iPhone *only*, Samsung *only*, or iPhone and Samsung.  

 

In this example, they would want account id #4 to fall into the bucket of iPhone and Samsung.  


So far my results are producing overlap which will cause query issues.  

Any assistance on how I should structure my query(ies) to produce these results would be helpful

 

Thank you,

  Dani


 

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • create table phone_data (id number, model text);
    
    insert into phone_data values (1,'iPhone'),(1,'Samsung'),(2,'iPhone'),(3,'Samsung'),(4,'iPhone'),(4,'Samsung'),(4,'LG');
    
    select * from phone_data;
    
    select id,
        case when s_iphone = true and s_samsung = true then 'iPhone and Samsung'
        when s_iphone = true and s_samsung = false then 'iPhone only'
        when s_iphone = false and s_samsung =true then 'Samsung only'
        end as bucket
    from (
    select id, sum(iff(model = 'iPhone',1,0))>0 as s_iphone, sum(iff(model = 'Samsung',1,0))>0 as s_samsung
    from phone_data
    group by id
    ) as A;
    
    gives:
    
    ID BUCKET
    1 iPhone and Samsung
    2 iPhone only
    3 Samsung only
    4 iPhone and Samsung
    Reply Like
  • Also if you don't a DB that supports IFF then CASE can be swapped in like so:

    select id,
        case when s_iphone = true and s_samsung = true then 'iPhone and Samsung'
        when s_iphone = true and s_samsung = false then 'iPhone only'
        when s_iphone = false and s_samsung =true then 'Samsung only'
        end as bucket
    from (
    select id
      ,sum(case when model = 'iPhone' then 1 else 0 end)>0 as s_iphone
      ,sum(case when model = 'Samsung' then 1 else 0 end)>0 as s_samsung
    from phone_data
    group by id
    ) as A;

    Hope that helps

    Reply Like
Like Follow
  • 9 mths agoLast active
  • 2Replies
  • 151Views
  • 2 Following