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,
CASE
WHEN COUNT(Unit_ID) = 1
AND SUM(Value) >= 50
THEN max(orderID)
ELSE NULL
END AS OrderID,
CASE
WHEN COUNT(Unit_ID) = 1
AND SUM(Value) >= 50
THEN max(orderdate)
ELSE NULL
END AS OrderDate
FROM Test
GROUP BY unit_id
HAVING SUM(value) >= 50
EDIT - I assumed that the new requirement was also using "50" rather than "10"?