1

Can anyone help me to how to achieve below result

Input data

ORDER_NUMBER    LINE_ITEM   FLAG
LP-13288    1   TRUE
LP-13288    2   FALSE
LP-13288    3   (null)
LP-13288    4   FALSE
LP-13289    1   FALSE
LP-13289    2   FALSE
LP-13290    1   TRUE
LP-13290    2   TRUE
LP-13290    3   TRUE
LP-13292    1   FALSE
LP-13293    2   TRUE

But I am expecting result like below:

ORDER_NUMBER    LINE_ITEM   FLAG    RESULT
LP-13288    1   TRUE    0
LP-13288    2   FALSE   0
LP-13288    3   (null)  0
LP-13288    4   FALSE   0
LP-13289    1   FALSE   0
LP-13289    2   FALSE   0
LP-13290    1   TRUE    1
LP-13290    2   TRUE    1
LP-13290    3   TRUE    1
LP-13292    1   FALSE   0
LP-13293    2   TRUE    1

Scenarios that need to implement

  1. For a group of order_number, if all line items are 'true' then result field need be '1'

  2. For a group of order_number, if any of line items contains mixed flags (true, false, null) then result field need to populate '0'

Any help would be appreciated.

  • Maybe you can show us better sample data. If result is not always 1 then include data where result is not 1. Also include the input data (table data) and not only the expected output. – miracle173 Aug 13 '18 at 22:11
5

You could use window aggregation (an aggregate function with an OVER clause) to apply the condition to a column of values whilst calculating the results for detail rows. Here is how you could use window aggregation in your situation:

SELECT
  order_number,
  line_item,
  flag,
  CASE COUNT(CASE flag WHEN 'true' THEN 1 END)
       OVER (PARTITION BY order_number)
  WHEN COUNT(*)
       OVER (PARTITION BY order_number)
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;

The COUNT(CASE flag WHEN 'true' THEN 1 END) OVER (PARTITION BY order_number) expression calculates the number of true values per order_number and returns the same value for rows that have the same order_number. As a result, you can generate values identical within groups of rows as well.


A variation on the above (using reverse logic and one count instead of two):

SELECT
  order_number,
  line_item,
  flag,
  CASE WHEN 
       COUNT(CASE flag WHEN 'true' THEN NULL ELSE 1 END)
       OVER (PARTITION BY order_number)
       = 0
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;
2

Slight modification gives desired output

 WITH true AS
 (SELECT COUNT(*) cnt,order_number FROM orders
 WHERE flag='true' GROUP BY order_number),

   total AS
 (
 SELECT COUNT(*) tot,order_number FROM orders 
 GROUP BY order_number
 )

SELECT order_number,line_item,flag,'0' RESULT FROM orders
WHERE order_number NOT IN (SELECT total.order_number 
 FROM true,total WHERE true.cnt=total.tot)
 UNION
SELECT order_number,line_item,flag,'1' RESULT FROM orders
WHERE order_number IN (SELECT total.order_number 
 FROM true,total WHERE true.cnt=total.tot)
 ORDER BY order_number,line_item;
  • Thanks Sam for you input, Actually if the flags was 'true' for all Line_Items for a order_number then the result need to be '1' else '0' sqlfiddle.com/#!4/ba02bf/1 – heye Aug 13 '18 at 20:10
  • But above example of order number AM-1328 doesn't fit what you were expecting now. shouldn't it be the result 0 for line items? – Sam Aug 13 '18 at 21:02
  • From sqlfillde except order number AM-13297 the rest should be '0' is that right? – Sam Aug 13 '18 at 21:11
  • you are right sam, But when I updated the input data it was showing wrong result. As per the scenario 1.) For a group of order_number, if all line items are 'true' then result field need be '1' 2.) For a order_number, if any line items contains mixed flags (true, false, null) then result field need to populate '0' please check this I have created some data scenario in it. from the example 'LP-13290' and 'LP-13293' should return result value as '1' and remaining all other orders_numbers need to return '0' Link: sqlfiddle.com/#!4/7ea72/2 – heye Aug 14 '18 at 5:13
1

Easily done with a CTE.

with cte as ( 
SELECT order_number, 
 SUM(CASE WHEN FLAG='true' THEN 1 ELSE 0 END) as flagsums, COUNT(*) as flagcounts 
    FROM orders
GROUP BY order_number
)
SELECT orders.*, CASE WHEN flagsums=flagcounts THEN '1' ELSE '0' END as RESULT 
FROM cte JOIN orders
     ON cte.order_number = orders.order_number ;

Basically, i'm aggregating the data down & getting the sum of flags for each order.

Then satisfy this condition:

So for a ORDER_NUMBER if a FLAG was true and remaining flags are false or null then it need populate the RESULT as '1' if all FLAG are true for a ORDER_NUMBER then RESULT should be '0'

... by simply checking if the flag sums per order are the same as the number of rows for that order.

  • Thanks for your input. I tired to use your logic but it was not working as expected. – heye Aug 13 '18 at 12:48
  • I am expecting the result field need to return only '0' only if ALL flags are TRUE, ELSE the result field need to be '1' all flags. Based on above table we are expecting all '1' in RESULT column. By using the CTE, it was populating '0' for true and '1' for remaining fields. – heye Aug 13 '18 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.