SELECT
    Name,
    COALESCE(Jam_count, 0) AS Jam_count
FROM (
    SELECT
        FROM_UNIXTIME(
            FLOOR(UNIX_TIMESTAMP(t_stamp) / 
                CEIL(TIMESTAMPDIFF(SECOND, :starttime, :endtime) / 24.0)
            ) * 
            CEIL(TIMESTAMPDIFF(SECOND, :starttime, :endtime) / 24.0)
        ) AS roundtime,
        Name,
        COUNT(*) AS Jam_count
    FROM (
        -- Jams from jam_area table
        SELECT Name, t_stamp
        FROM jam_area
        WHERE t_stamp BETWEEN :starttime AND :endtime
        
        UNION ALL
        
        -- Jams from lane_data table (only where Jam = 1)
        SELECT Name, t_stamp
        FROM lane_data
        WHERE t_stamp BETWEEN :starttime AND :endtime
            AND Jam = 1
    ) AS combined_jams
    GROUP BY roundtime, Name
    ORDER BY roundtime ASC
) basa;

