List all Active groups with no members or zero members SQL query

Document ID : KB000101601
Last Modified Date : 13/12/2018
Show Technical Document Details
Question:
How do list all Service Desk active Groups with no members or zero members in SQL query? 
Answer:
In SQL Studio below query would return all Service Desk active groups with no members.

SELECT CNT.last_name AS GroupName,COUNT(MEM.id) AS COUNT FROM MDB.dbo.grpmem MEM WITH (NOLOCK) FULL OUTER JOIN mdb.dbo.ca_contact CNT WITH (NOLOCK) ON MEM.group_id = CNT.contact_uuid FULL OUTER JOIN mdb.dbo.ca_contact CNT1 WITH (NOLOCK) ON MEM.member = CNT1.contact_uuid WHERE CNT.contact_type = '2308' AND CNT.inactive = 0 AND MEM.member IS NULL GROUP BY CNT.last_name ORDER BY COUNT ASC