CASE WHEN - Multiple Conditions - Over Multiple Rows of same reference-CodePudding

I am trying to create a flag by using CASE WHEN to determine something but it keeps returning "single_state" - I guess because its not applying the CASE WHEN to all rows with o/n 1234 which is the bit I need help with.

My expected output is to have "multi_state" for the first 3 lines as they are all the same o/n and "single_state" for the last line as its neither grouped or got more than 1 state.

Example below:

DATA Table

o/n      sku     order_type    state   state_check
1234    ABC987    Grouped     express
1234    BCD654    Grouped     arrived
1234    CDF321    Grouped     shipped
5679    GBT104   Not Grouped  express

Query

SELECT o/n, SKU, order_type, state, 
CASE WHEN order_type is "Grouped" AND (state = "express" AND state = "arrived") THEN "multi_state"
CASE WHEN order_type is "Grouped" AND (state = "express" AND state = "shipped") THEN "multi_state"
ELSE "single_state"
END AS state_check
FROM data.table

CodePudding user response：

The CASEs for multi_state both check that state has the values express and arrived/shipped at the same time. A single column cannot have multiple values at the same time.

You can use IN() to accept multiple values as multi_state:

SELECT o/n
    , sku
    , order_type
    , state
    , CASE
        WHEN order_type = 'Grouped' AND state IN('express', 'arrived', 'shipped') THEN 'multi_state'
        ELSE 'single_state'
    END AS state_check
FROM data.table

CodePudding user response：

You need a window function (an aggregation function with an OVER clause) to look at multiple rows at once.

SELECT 
  "o/n", sku, order_type, state,
  CASE
    WHEN order_type <> 'Grouped' THEN
      'single_state'
    WHEN COUNT(DISTINCT state) OVER (PARTITION BY "o/n", order_type) = 1 THEN
      'single_state'
    ELSE
      'multi_state'
  END
FROM data.table
ORDER BY "o/n", sku;