Can you try this?

select order_header.client_id as clientid,
DECODE('Shipped', 'Hold', order_header.ORDER_DATE, 'Shipped', order_header.DELIVER_BY_DATE, NULL) as TheDate,
count(order_header.FROM_SITE_ID)
from order_header
group by client_id, client_id, order_header.ORDER_DATE, order_header.DELIVER_BY_DATE

It should work. Good lock!