PostgreSql - How to create conditional column with the filter on another column?

user_10003538 0
Viewed 1670

I want to add 1 more columns where segment out whether the customer had sold at least one product or not. Data example:

ProductID    Customer     Status
1            John         Not sold
2            John         Not Sold
3            John         Sold

My expect result

ProductID    Customer     Status         Sold_at_least_1
1            John         Not sold       Yes
2            John         Not Sold       Yes
3            John         Sold           Yes
4            Andrew       Not Sold       No
5            Andrew       Not Sold       No
6            Brandon      Sold           Yes

This is an example data. Sorry for any inconvenience as I unable to extract data out. Btw, appreciating for any helps.

window-functions postgresql sql
2 Answers

You can do a window count of records of the same customer that have status = 'Sold' in a case expression:

select 
    t.*, 
    case when sum( (status = 'Sold')::int ) over(partition by customer) >= 1
        then 'Yes'
        else 'No'
    end
from mytable

NB: note that this does not magically create new records (as shown in your sample data). This query gives you as many records in the resultset as there are in the table, with an additionnal column that indicates whether each cutsomer has at least one sold item in the table.

Here is a demo provided by VBokšić (thanks).

user_106767160

Another option is to use bool_or() as a window function. If you can live with a boolean column rather than a varchar with Yes/No, this makes the expression even simpler:

select productid, customer, status, 
       bool_or(status = 'Sold') over (partition by customer) as sold_at_least_one
from mytable;

Online example: https://rextester.com/NDN54253

user_3303150
Related