SQL group by return null if one is null

I have orders table that have delete marker column date_deleted which value is null or date

order_number	item_id	date_deleted
111	aaa	null
111	bbb	null
111	ccc	2021-x-x

I have query to select the fully deleted orders with group by order_number

SELECT order_number, 
       date_deleted 
FROM orders 
WHERE date_deleted IS NOT NULL 
GROUP BY order_number

It give me this result

order_number	date_deleted
111	2021-x-x

Which indicate that the whole order is deleted but is not.

Is there a way to get null when not all of them is null?

Answer

You can use a case expression to return the latest deleted where all records are deleted, and where any are NULL the expression will return NULL:

SELECT order_number, 
        CASE WHEN COUNT(*) = COUNT(date_deleted) THEN MAX(date_Deleted) END AS date_deleted
FROM orders 
GROUP BY order_number;

You may also wish to return the earliest deleted date with MIN(date_deleted), the point is though, if you group by order_number you can only return one date_deleted and you have to tell the query engine which one you want to return by using some kind of additional logic like MIN or MAX.