SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE eployees ( `name` VARCHAR(64), `status` VARCHAR(64) ); INSERT INTO eployees VALUES ("john", "delivered"), ("john", "delivered"), ("john", "delivered"), ("david", "submitted"), ("david", "delivered"), ("david", "created"), ("smith", "submitted"), ("smith", "created"), ("smith", "submitted"), ("krish", "created"), ("avnee", "delivered"), ("avnee", "delivered"), ("avnee", "delivered"), ("avnee", "submitted"), ("sruti", "submitted"), ("sruti", "created"), ("sruti", "submitted"), ("michael", "delivered"), ("michael", "delivered"), ("michael", "delivered"), ("zoe", "created"), ("harris", "submitted"), ("harris", "delivered"), ("harris", "created"), ("emmy", "delivered"), ("emmy", "delivered"), ("emmy", "delivered"), ("hanna", "submitted"), ("hanna", "created"), ("hanna", "submitted"); SELECT `name`, CASE WHEN `statuses` NOT LIKE "%submitted%" AND `statuses` NOT LIKE "%created%" THEN 'completed' WHEN `statuses` LIKE "%submitted%" AND `statuses` NOT LIKE '%delivered%' THEN 'awaiting progress' WHEN `statuses` LIKE '%submitted%' AND `statuses` LIKE '%delivered%' THEN 'in progress' WHEN `statuses` LIKE '%created%' THEN 'awaiting submission' END `status` FROM ( SELECT `name`, GROUP_CONCAT(`status`) `statuses` FROM eployees GROUP BY `name` ) emploees_statuses;