Here is my attempt - summing the value by unit_id, displaying a "Y" flag if there is a single unit_id with value >= 50 and only displaying results if the sum of value is >= 50.
SELECT unit_id,
SUM(value) AS unit_total,
CASE
WHEN COUNT(Unit_ID) = 1
AND SUM(value) >= 50
THEN 'Y'
ELSE 'N'
END AS Flag
FROM Test
GROUP BY unit_id
HAVING SUM(value) >= 50