Yenky Yenky - 6 months ago 11
SQL Question

MySQL return max value or null if one column has no value

I try to get the max value of a mysql select, but want to have it null/empty/0 if there is one row containing no timestamp.

Table stats (simplyfied):

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


I'm now looking to get the lowest order date (no problem, as it is never empty), and
the maximum cancel date. If the client has already cancelled his subscription, the cancel date is filled, but if he is still active, there is no cancel date at all.

Query:

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


Returns:

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


I can't figure it out how to return empty/0/NULL if there is one (or more) empty colums for a client. Also tried with NULL fields.

Thanks for any hint.

Answer

I don't know how fast it will be but I guess it can be solved like this:

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

I couldn't test it but the idea behind this solution is that count(cancel_date) should count all not null value entries and if it's equal to count(*) that means that there are no null values and it will return max(cancel_date), otherwise null.