Fill empty row when have one id record on summary query in Mysql

Question

Good afternoon, i want to ask about MySql query.

I have the initial data as pict below.

initial datas

I want to calculate the number of hours difference by displaying several data columns with the MySql query below.

SELECT id AS id,beacon_name AS Name,MIN(received_date) as tap_in,MAX(received_date) as tap_out,TIMESTAMPDIFF(HOUR,MIN(received_date),MAX(received_date)) AS work_hours from tb_gate_log where date(received_date) ='2023-06-06' group by id;

and it works like pict below.

result

however, there are cases such as there is only one data with status only "OUT" or "IN" for one id. I want the result like pict below when have status "Out" or "IN" only in one id. enter image description here

how i do the query with result i wanted?

thank you,

regards.

• Rick James: It sounds like you need a table with all possible values, then LEFT JOIN from that table to your table. You may also need a COALESCE to turn NULL into 0.

Answer 1

SELECT id AS id,
 beacon_name AS Name,
 MIN(case when status = 'IN' then received_date end) as tap_in,
 MAX(case when status = 'OUT' then received_date end) as tap_out,
 COALESCE(TIMESTAMPDIFF(HOUR,
 MIN(case when status = 'IN' then received_date end),
 MAX(case when status = 'OUT' then received_date end)), 0) AS work_hours 
from tb_gate_log 
where date(received_date) ='2023-06-06' 
group by id
;

• Community: Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.