T-SQL - AOAG Health state Healthy or Not Healthy in a Single row I am trying to write a small piece of code which should give the status of the AOAG SQL Server 2014 Health or not healthy based on below two conditions .

Mine is 3 Node replica AOAG (including BCP Replica).

Conditions I am looking for :


If the synchronization_health_desc is "Healthy" for all three replicas , I should get the result as below :





If the synchronization_health_desc is "Not Healthy" for any of the three replicas , I should get the result as below :




So far I have tried below :

select dns_name as AG_Listener ,
(select top 1 group_name  from sys.dm_hadr_availability_replica_cluster_nodes  ) as AG_Gorup,
synchronization_health_desc as AG_Status
from sys.dm_hadr_availability_replica_states rs 
INNER JOIN  sys.availability_group_listeners gl ON rs.group_id = rs.group_id 


The output is below . But I want only one row as result which says overall result as Healthy or Not Healthy based on above two conditions .
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.