T-SQL - AOAG Health state Healthy or Not Healthy in a Single row
You have two states - everything is healthy or something is not healthy. This applies per availability group. So if anything's health is 'NOT_HEALTHY' that's the group's status, otherwise the status is 'HEALTHY'.

There are various ways to find if anything is unhealthy. One is EXISTS. Another is aggregate functions, some of which also work on character types. MAX() &amp; MIN() are the useful ones. In a dictionary sort 'NOT_HEALTHY' > 'HEALTHY' so MAX('HEALTHY', 'NOT_HEALTHY') will return 'NOT_HEALTHY'. There is only one MAX in any GROUP BY so this removes the need for TOP clauses.

The query will look like 

select
  case max (synchronization_health_desc)
    when 'NOT_HEALTHY' then 'NOT_HEALTHY'
    else 'HEALTHY'
  end
from &lt;those tables, joined&gt;
group by
  group_name


Once the GROUP BY is introduced other returned columns must also be in aggregate functions or mentioned in the GROUP BY clause.