SQL group by return null if one is null

user_3564750 0
Viewed 463

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?

group-by mysql sql
2 Answers

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.

user_10484250

This will only return fully deleted orders.

If you join on itself and check for NULL values(not deleted rows/parts) in the join, than set "if the parts IS NULL" it only included orders where there are no NULL values in the date_deleted part(and thus fully deleted).

This also does not use COUNT or other groupings that could slow the query down so should be fast and readable.

SELECT 
    order_number, 
    date_deleted 
FROM orders 
    LEFT JOIN orders openOrderParts ON openOrderParts.order_number = openOrderParts.order_number 
        AND openOrderParts.date_deleted IS NULL
WHERE date_deleted IS NOT NULL AND openOrderParts IS NULL
GROUP BY order_number

But you have to remember, it is grouped by order number, do "date_deleted" could contain more than ne value but you are just showing one. you could consider using GROUP_CONCAT there to optionally show all if any.

user_59248480
Related