CODEWITHSUNDEEP
mysqlmaxmin
cglvli
cglvli

Reputation: 137

MySQL - MIN and MAX value with empty fields

I'm trying to write an SQL that will allow me to return the minimum value for a start time and a maximum time for an end time. My problem is that in the table I can have empty rows and when I do a MIN on an empty field it returns me an empty value. I can't do begin_service! = '' because I may not have values, in this case I must have an empty result. This is my table :

app_id function_id begin_service end_service
B125 12
B125 13
B125 54
C789 98
C789 12 06:00 18:00
C789 15 08:00 20:00
C789 78

My SQL :

SELECT app_id, MIN(begin_service), MAX(end_service)
FROM applications
GROUP BY app_id;

Result :

app_id begin_service begin_service
B125
C789 20:00

Desired result :

app_id begin_service begin_service
B125
C789 06:00 20:00

Can you help me ?

Upvotes: 0

Views: 233

Answers (1)

Barmar
Barmar

Reputation: 781503

Use two subqueries to get the empty and non-empty minimum values for each app_id. Combine them with UNION and then take the max of that to prefer the non-empty value.

SELECT app_id, MAX(begin_service) AS begin_service, MAX(end_service) AS end_service
FROM (
    SELECT app_id, MIN(begin_service) AS begin_service, MAX(end_service) AS end_service
    FROM applications
    WHERE begin_service != ''
    GROUP BY app_id

    UNION ALL

    SELECT app_id, '', MAX(end_service)
    FROM applications
    WHERE begin_service = ''
    GROUP BY app_id
) AS x
GROUP BY app_id

Upvotes: 1

Related Questions