Can't get CASE and GROUP BY to show all values in the same row-CodePudding

I'm struggling to find a fix for this issue. This is my query:

SELECT
    time,
    enrolment,
    come,
    cena,
    tipo_ausencia

FROM (
        SELECT
            DATE_FORMAT(created_at, '%d/%m/%Y') AS time,
            enrolment_id as enrolment,
            (CASE 
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='3' AND permits.cancel <1 THEN 'No almuerza'
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='4' AND permits.cancel <1 THEN 'Come picnic'
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='1' AND permits.cancel <1 THEN 'Almuerza antes'
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='2' AND permits.cancel <1 THEN 'Almuerza después'
                WHEN permits.permit_type_id= '4' AND permits.status_id = '2' AND permits.permit_type_option_id='5' AND permits.cancel <1 THEN 'Almuerzo con nota'
                ELSE null END  
              ) as come,
              (CASE 
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='8'  THEN 'No cena'
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='9'  THEN 'Cena picnic'
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='6'  THEN 'Cena antes'
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='7'  THEN 'Cena después'
                WHEN permits.permit_type_id= '5' AND permits.status_id = '2' AND permits.permit_type_option_id='10' THEN 'Cena con nota'

               ELSE null END  
              )as cena,
            (CASE 
            WHEN permits.permit_type_id= '6' AND permits.status_id = '2' AND permits.cancel <1 AND DATE(NOW()) between date_start and (date_end-1) THEN 'Duerme fuera'
            WHEN permits.permit_type_id= '7' AND permits.status_id = '2' AND permits.cancel <1 AND (date_start = curdate() OR date_end > curdate()) THEN 'Regresa a dormir'
            ELSE null END  
          ) as tipo_ausencia
        FROM        
            permits
   WHERE (DATE(NOW()) between date_start and (date_end-1) OR (date_start = curdate() OR date_end > curdate())) AND permits.cancel <1
    ) p
      
    
 GROUP by 1,2,3,4,5
 order by 2 desc

This is the result:

I get 3 rows for the same "enrolment" field. I'm trying to get something like:

time | enrolment | come | cena | tipo_ausencia

19/09/2021 | 101 |no almuerza |no cena | duerme fuera

Any ideas? Thanks!

CodePudding user response：

Use MAX() for converting multiple rows to one row of each enrollment WHERE one column have value others two columns are NULL.

SELECT time
     , enrolment
     , MAX(come) come
     , MAX(cena) cena
     , MAX(tipo_ausencia) tipo_ausencia
FROM (
        Your main query
     ) p
GROUP BY time
       , enrolment
ORDER BY enrolment desc

Alternative way, no need to use subquery only main query will serve purpose

SELECT DATE_FORMAT(created_at, '%d/%m/%Y') AS time
     , enrolment_id as enrolment
     , MAX(case logic for come place here) as come
     , MAX(case logic for cena place here) as cena
     , MAX(case logic for tipo_ausencia place here) as tipo_ausencia
FROM permits
WHERE (DATE(NOW()) between date_start and (date_end-1) OR (date_start = curdate() OR date_end > curdate())) 
     AND permits.cancel <1
GROUP BY DATE_FORMAT(created_at, '%d/%m/%Y')
       , enrolment_id
ORDER BY enrolment_id DESC

More readable CASE statement for come. If data type of permit_type_id, status_id and permit_type_option_id are integer then not to use quation mark.

CASE 
     WHEN permit_type_id = '4' AND status_id = '2' AND cancel < 1
         THEN CASE permit_type_option_id
                  WHEN '3' THEN 'No almuerza'
                  WHEN '4' THEN 'Come picnic'
                  WHEN '1' THEN 'Almuerza antes'
                  WHEN '2' THEN 'Almuerza después'
                  WHEN '5' THEN 'Almuerzo con nota'
              END
     ELSE NULL
END