How to write an SQL query to get the number of completed and cancelled deliveries by excluding the clone delivery cancellation

Question

Id	deliverytype	parentId	status
1	parentDelivery1	0	cancelled
2	cloneDelivery1a	1	cancelled
3	cloneDelivery1b	1	completed
4	cloneDelivery1c	1	cancelled
5	parentDelivery2	0	cancelled
6	cloneDelivery2a	5	cancelled
7	cloneDelivery2b	5	cancelled
8	cloneDelivery 2c	5	cancelled
9	parentDelivery3	0	completed
10	parentDelivery4	0	cancelled

expected output(for this example):

number of completed deliveries	number of cancelled deliveries
2	2

All parent deliveries have 0 as parent id and clone deliveries have its parentId in parentId column Here if even 1 delivery out of parent and clones is completed then it should count as completed and all clone cancellation should be eliminated.
*Parent deliveries can have 0 clones
*Clone deliveries will not have clone of its own
*If Parent delivery is cancelled but any 1 of its clone is completed then it should be considered as 1 completed and 0 cancelled(the other cancellations should not be counted under cancelled deliveries)

If all the delivery out of parent and clones is cancelled then it should be counted as 1 cancellation. How can I write an SQL query for this?

I am using MySQL workbench community version 8.0.29

Answer 1

My paraphrased logic...

• if the parent says completed, it is completed
• if parent says cancelled, but any children say completed, it is completed
• else, parent and all children say cancelled, so it is cancelled

Which gives...

WITH
  parent_outcome AS
(
  SELECT
    p.id,
    CASE
      WHEN MAX(p.status) = 'completed' THEN 1
      WHEN MAX(c.status) = 'completed' THEN 1
      ELSE 0
    END
      AS is_completed
  FROM
    your_table AS p
  LEFT JOIN
    your_table AS c
       ON c.parent_id = p.id
  WHERE
    p.parent_id = 0
  GROUP BY
    p.id
)
SELECT
             SUM(is_completed) AS is_completed,
  COUNT(*) - SUM(is_completed) AS is_cancelled
FROM
  parent_outcome