I see your query failed a syntax check; when i reformatted it, i see you got three trailing ,0), as if they were part of NULLIF/COALESCE, but you need only two,.


SELECT coalesce (o.customerid,'GrandTotal') AS CUSTID
,[AVG SHELF DELIV] = COALESCE(convert(DECIMAL(5,1),
SUM(case
   WHEN o.Status in ('ready','stock present')
   AND o.Status not in ('delivered')
   THEN 1
   ELSE 0
   END
  ))
/
NULLIF(
   SUM(
    CONVERT(DECIMAL(5,1),
            CASE
             WHEN o.Status = 'delivered and approved'
             AND o.Deliveryconfirmed >= getdate()-7
             AND ((DATEPART(dw, o.Deliveryconfirmed) + @@DATEFIRST) % 7) NOT IN (0, 1)
             THEN 1
             ELSE 0
            END
       )
    )--sum
   ,0)--nullif
,0) --HIGHEST coalesce
* .143

from
orders o
group by rollup (customerid)