Update new column using If sum condition on existing fields

unique_id site status Final status HMEX093 MEX025 Accepted Incomplete HMEX093 HMEX056 Accepted Incomplete HMEX093 LTY0014 Pending Incomplete

I want an update query to update Final status column depending if query on status. every unique_id has multiple sites with different status. I want to update final status column with if sum of condition. for eg. unique_id HMEX093 has 3 sites with different sites. I want to check status of these sites,if sum(status='Accepted') of site >= 3 final status column should be updated with value Approved. If there is atleast one pending status to corresponding sites of unique_id, final status shoul be updated as Incomplete as shown above. please help. I tried with Case when statement, but no success.