MySQL returns the maximum value or null if one column does not matter

Question

I am trying to get the maximum mysql select value, but want to have it null / empty / 0 if there is one row not containing a timestamp.

Statistics table (simple):

ID CLIENT ORDER_DATE CANCEL_DATE 1 5 1213567200 2 5 1213567200 3 6 1210629600 1281736799 4 6 1210629600 1281736799 5 7 1201042800 1248386399 6 7 1201042800 7 8 1205449200 1271282399 

Now I want to get the date of the lowest order (no problem, since it is never empty), and the maximum cancellation date. If the customer has already canceled their subscription, the cancellation date will be filled out, but if it is still active, the cancellation date is completely absent.

Query:

 SELECT ID, min(ORDER_DATE) AS OD, max(CANCEL_DATE) AS CD FROM stats GROUP BY CLIENT 

Return:

 ID OD CD 5 1213567200 // fine 6 1210629600 1281736799 // fine 7 1201042800 1248386399 // Should be empty 8 1205449200 1271282399 // fine 

I cannot figure out how to return empty / 0 / NULL if there are one (or several) empty numbers for the client. Also tried with NULL fields.

Thanks for any hint.

Answer 1

I don’t know how fast it will be, but I think it can be solved as follows:

 SELECT ID, min(ORDER_DATE) AS OD, IF(COUNT(*)=COUNT(CANCEL_DATE),max(CANCEL_DATE),NULL) AS CD FROM stats GROUP BY CLIENT 

I could not test it, but the idea of ​​this solution is that count(cancel_date) should count all non-zero values, and if it is equal to count(*) , it means that there are no zero values, and it will return max(cancel_date) otherwise null.

Answer 2

You can use this query:

 SELECT client, min(ORDER_DATE) AS OD, case when MAX(CANCEL_DATE IS NULL)=0 THEN max(CANCEL_DATE) END AS CD FROM stats GROUP BY CLIENT 

See the fiddle here .

• CANCEL_DATE IS NULL will evaluate to either 0 when CANCEL_DATE is not null or 1 when it is null
• MAX(CANCEL_DATE IS NULL) will be evaluated to 0 if there is no cancel_date with zero values, otherwise its value will be 1.
• When MAX(CANCEL_DATE IS NULL)=0 this means that there are no rows where CANCEL_DATE is null, in which case we need to return MAX (cancel_date), otherwise we need to return NULL.