SELECT 
    CONCAT(DATE(t_stamp), ' ', HOUR(t_stamp), ':00') AS `Start Timestamp`,
    CONCAT('H', TIMESTAMPDIFF(HOUR, DATE_FORMAT(t_stamp, "%Y-%m-%d %H:00:00"), DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))) AS `Hour`,
    sLocation_ID AS `Location ID`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 0 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `Sorted`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 1 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `Unknown`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 2 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `Unexpected`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 3 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `TrackingError`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 4 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `GapError`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 5 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `DestinationFull`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 6 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `DestinationFault`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 7 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `DestinationInvalid`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 12 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `DestinationDisabled`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 13 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `ThroughputLimit`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 14 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `DivertFail`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 16 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `DestinationNone`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 17 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `Lost`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 18 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `DimensionError`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 19 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `WeightError`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 20 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `ContainerUtilization`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 21 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `UnableToDivert`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 = 22 THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `DestinationNotAttempted`,
    CONCAT(CASE WHEN COUNT(*) = 0 THEN 0 ELSE ROUND((SUM(CASE WHEN adiSort_Code_0 IN (8, 9, 10) THEN 1 ELSE 0 END) * 100.0) / COUNT(*), 2) END, '%') AS `ScanError`
FROM item_data
WHERE t_stamp BETWEEN :starttime AND :endtime
    AND adiSort_Code_0 NOT IN (11, 15)
    AND (sLocation_ID = :locationid OR :locationid IS NULL OR :locationid = '')
GROUP BY DATE(t_stamp), HOUR(t_stamp), sLocation_ID
ORDER BY DATE(t_stamp) ASC, HOUR(t_stamp) ASC;