How to only count the Order finish if all items status from order finish-CodePudding

I have a tabel orders in mysql

And the Items detail from each order:

I would like to count the summary of orders by status in the order table and items table, with expected result:

I tried with this query:

SELECT 
count(case when o.status = 'received' THEN 1 ELSE 0 END) AS received,
count(case when i.status = 'finish' THEN 1 ELSE 0 END) AS finish,
count(case when o.status = 'cancel' THEN 1 ELSE 0 END) AS cancel
    FROM 
     orders o INNER JOIN items i on o.id=i.order_id
    GROUP BY o.id, i.status
    HAVING i.status = 'finish'

Looks like in the finish column still counting all of items in the order it self. I would like to count the finish column as 1 if the all items from order in status finish

CodePudding user response：

To confirm, an order could be made up of many sub-parts. The completion status of any sub-part may (or not) be complete. You are looking to get all orders where all their sub-parts are complete. Is that correct?

If so, you can do a query by counting the order details such that all the entries marked as 'finish' is equal to the count of entries for that same order. Once that result is qualified, you can get the details from the main order such as on who the customer is, etc.

select
      PreQueryResult.orderID,
      PreQueryResult.TotalSubParts,
      PreQueryResult.FinishedSubParts,
      o.date,
      o.status
   from
      ( select
              od.orderid,
              count(*) as TotalSubParts,
              sum( case when od.status = 'finish'
                        then 1 else 0 end ) as FinishedSubParts
           from
              order_detail od
           group by
              od.orderid ) PreQueryResult
        JOIN Orders o
           on PreQueryResult.orderid = o.orderid

So if you run the above query, you will see the results with the total parts and the total finished. To limit your final result set to just those complete, just add a final WHERE clause such as

   where
      PreQueryResult.TotalSubParts = PreQueryResult.FinishedSubParts

But your consideration of status of ordered cancelled, you can tack wrap the above up into yet another layer to get your counts such as grouped by day

I would like to count the summary of orders by status in the order table and items table, with expected result: enter image description here

select
      Final.date,
      sum(case when Final.status = 'received' THEN 1 ELSE 0 END) received,
      sum(case Final.TotalSubParts = PreQueryResult.FinishedSubParts
                THEN 1 ELSE 0 END) finish,
      sum(case when o.status = 'cancel' THEN 1 ELSE 0 END) cancel
   from
      (entire query from above without where clause) Final
   group by
      Final.date