MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH yourNewTable AS (

SELECT 1 AS id, 'Front' AS direction, 'Done' AS status UNION ALL

SELECT 1, 'Rear', 'Done' UNION ALL

SELECT 1, 'Left', 'Done' UNION ALL

SELECT 1, 'Right', 'Done' UNION ALL

SELECT 1, 'Up', 'Done' UNION ALL

SELECT 1, 'Down', 'Done' UNION ALL

SELECT 2, 'Front', 'Done' UNION ALL

SELECT 2, 'Rear', 'Done' UNION ALL

SELECT 2, 'Left', 'Done' UNION ALL

SELECT 2, 'Right', 'Done' UNION ALL

SELECT 2, 'Up', 'No Need' UNION ALL

SELECT 2, 'Down', 'No Need' UNION ALL

SELECT 3, 'Front', 'Done' UNION ALL

SELECT 3, 'Rear', 'Ongoing' UNION ALL

SELECT 3, 'Left', 'Ongoing' UNION ALL

SELECT 3, 'Right', 'Done' UNION ALL

SELECT 3, 'Up', 'Done' UNION ALL

SELECT 3, 'Down', 'Not Yet' UNION ALL

SELECT 4, 'Front', 'Not Yet' UNION ALL

SELECT 4, 'Rear', 'Not Yet' UNION ALL

SELECT 4, 'Left', 'Not Yet' UNION ALL

SELECT 4, 'Right', 'Not Yet' UNION ALL

SELECT 4, 'Up', 'Not Yet' UNION ALL

SELECT 4, 'Down', 'Not Yet'

)

SELECT

id,

100.0 * AVG(status IN ('Done', 'No Need')) AS pct,

CASE WHEN SUM(status NOT IN ('Done', 'No Need')) = 0 THEN 'Complete'

WHEN SUM(status = 'Ongoing') > 0 THEN 'Ongoing'

WHEN SUM(status = 'Not Yet') = 6 THEN 'Not Yet'

ELSE 'Unknown' END AS status

FROM yourNewTable

GROUP BY