count

[nikko50]

Hi all.
I use the below query to count all the items on a customers order and see if all the items are marked "SHIPPED" or "DIRECT" or "CANCEL". If "ITEMS" and "SHIPPED" equal then the order is considered Closed. Now how can I alter the below query to ONLY return the orders from my CUSTOMER_ORDERS table where the count of "ITEMS" and "SHIPPED" do not match?

SELECT COUNT(*)  AS ITEMS
            , COUNT(CASE WHEN itemstatus = 'SHIPPED'
                                    OR
                         itemstatus =  'DIRECT'
                                    OR
                         itemstatus =  'CANCEL'
              THEN 937 END)            AS SHIPPED
FROM CUSTOMER_ORDERS

[Lars_Berg]

I assume you want to retreive some identification for the order as well

select orderNumber,
          count(*) as items,
          count(case when itemstatus in ('SHIPPED','DIRECT','CANCEL') then 4711 end) as shipped
  from customer_orders
 group by order_number
having count(*) <> count(case when itemstatus in ('SHIPPED','DIRECT','CANCEL') then 4711 end)