Oracle Counts of partitions based on conditions

I have following data

I need to count the orders which skipped a particular status. This is a status history table where statuses are arranged in descending order.

I have tried below Oracle SQL Logic but it's giving me count of all individual records and not the individual orders

I need this to be part of select statement only and not in whare clause or CTE or subquery as I'd be replicating this in OBIEE Answers.

select
count(CASE WHEN STATUS NOT LIKE '%Approved%' THEN 1 END) NOT_APPROVED,
count(CASE WHEN STATUS NOT LIKE '%Dispatched%' THEN 1 END) NOT_DISPATCHED
From Orders 

I would suggest changing the approach a bit:

1. For each order, aggregate all the statuses, sorted by the creation date of the row
2. Look for the missing status in the list

SELECT count(
       CASE
         WHEN INSTR(status_array, 'Approved') > 0 THEN 0
         ELSE 1) as 'Not approved', 
       ... Repeat for each status
FROM (
  SELECT order_id, LISTAGG(status, ',') WITHIN GROUP (ORDER BY created_at) AS status_array
  FROM Orders
  GROUP BY order_id
) t;

Please note that that's just the general idea, I don't have your data, so I can't execute the query for you.

You need to include a GROUP BY clause and can use conditional aggregation:

SELECT order#,
       MAX(CASE status WHEN 'Fulfilled' THEN 1 ELSE 0 END) AS is_fulfilled,
       MAX(CASE status WHEN 'Dispatched' THEN 1 ELSE 0 END) AS is_dispatched,
       MAX(CASE status WHEN 'Approved' THEN 1 ELSE 0 END) AS is_approved,
       MAX(CASE status WHEN 'Cancelled' THEN 1 ELSE 0 END) AS is_cancelled,
       MAX(CASE status WHEN 'Initiated' THEN 1 ELSE 0 END) AS is_initiated
FROM   orders
GROUP BY order#

Which, for the sample data:

CREATE TABLE orders (order#, status) AS
SELECT 'A123', 'Fulfilled' FROM DUAL UNION ALL
SELECT 'A123', 'Dispatched' FROM DUAL UNION ALL
SELECT 'A123', 'Approved' FROM DUAL UNION ALL
SELECT 'A123', 'Initiated' FROM DUAL UNION ALL
SELECT 'A456', 'Cancelled' FROM DUAL UNION ALL
SELECT 'A456', 'Initiated' FROM DUAL UNION ALL
SELECT 'B123', 'Fulfilled' FROM DUAL UNION ALL
SELECT 'B123', 'Dispatched' FROM DUAL UNION ALL
SELECT 'B123', 'Initiated' FROM DUAL UNION ALL
SELECT 'B456', 'Fulfilled' FROM DUAL UNION ALL
SELECT 'B456', 'Approved' FROM DUAL UNION ALL
SELECT 'B456', 'Initiated' FROM DUAL;

Outputs:

fiddle