Identifying products that a customer owns

I want to check whether my customer owns an iPhone, iPad, or Macbook. I am not looking for how many iPhone or iPad or Macbook one customer has, I just want to identify if they own any of them, or if they own any two, or if they own all three.

I am thinking of using the CASE WHEN function and here is the query:

select customer
, case when AppleProduct = 'iPhone' THEN 'TRUE' ELSE 'FALSE END AS Owns_iPhone
, case when AppleProduct = 'iPad' THEN 'TRUE' ELSE 'FALSE AS Owns_iPad
, case when AppleProduct = 'Macbook' THEN 'TRUE' ELSE 'FALSE' AS Owns_Macbook
from Apple_Product_Ownership

This is the result that I am trying to get

customer | Owns_iPhone | Owns_iPad | Owns_Macbook
X          TRUE          TRUE         FALSE
Y          FALSE         TRUE         TRUE
Z          TRUE          FALSE        FALSE

But this is what I am getting instead

customer | Owns_iPhone | Owns_iPad | Owns_Macbook
X          TRUE          FALSE       FALSE
X          FALSE         TRUE        FALSE
Y          FALSE         TRUE        FALSE
Y          FALSE         FALSE       TRUE
Z          TRUE          FALSE       FALSE

Answer

You are looking for conditional aggregation. I would phrase your query as:

select 
    customer,
    logical_or(AppleProduct = 'iPhone') Owns_iPhone,
    logical_or(AppleProduct = 'iPad') Owns_iPad,
    logical_or(AppleProduct = 'Macbook') Owns_Macbook,
from Apple_Product_Ownership
group by customer

Advertisement

Answer