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"?