I have a customer transaction table that looks similar to this:

It contains all transactions over a long time period I want to know which customers have bought just red, just blue and both red and blue, so a table like this:

I've attempted a CASE WHEN statement, but it ends up duplicating the customer IDs But when I group by customer ID then I get an error saying the case statement isn't aggregating

CodePudding user response：

Conditional aggreagation could be used:

SELECT CustomerID,
     CASE WHEN COUNT(DISTINCT Product) > 1THEN 'Many'
          WHEN COUNT_IF(Product='Red') > 0 THEN 'Red only'
          WHEN COUNT_IF(Product='Blue') > 0 THEN 'Blue only'
     END
FROM tab
GROUP BY CustomerID;

CodePudding user response：

So if you have multiple product values Lukasz solution work if you add a filter to exclude the other values, like so:

SELECT CustomerID,
     CASE WHEN COUNT(DISTINCT Product) = 2 THEN 'Both'
          WHEN COUNT_IF(Product='Red') > 0 THEN 'Red only'
          WHEN COUNT_IF(Product='Blue') > 0 THEN 'Blue only'
     END
FROM tab
WHERE Product in ('Red','Blue')
GROUP BY CustomerID;

otherwise a Orange would throw off the answer.