Body: <p>Is there anyway (using SearchKit or any other way) I can display a list of contacts who are in more than 1 smart group? I have 160,000 contacts and I need to make sure that my contacts are only in 1 smart group. I cannot see a way to do this.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-08-29T13:29:18.833Z
Comments.Text: How many smart groups do you have? If 2, then it'd be easy enough to search for contacts in both groups.
  If > 2... that's trickier.
Comments.UserDisplayName: Coleman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-08-29T18:07:43.073Z
Comments.Text: I have 24 Smart Groups, with about 9000 contacts spread across these groups. I’m certain I’ve got contacts in more than 1 smart group, which I don’t want.
Comments.UserDisplayName: Steve Middleton
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-08-29T18:46:42.690Z
Comments.Text: I think you'd need to write a bit of sql for that. Basically you need to trigger all smart groups to rebuild, then `SELECT contact_id FROM civicrm_group_contact_cache WHERE COUNT(contact_id) > 1 GROUP_BY contact_id;`
Comments.UserDisplayName: Coleman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-09-01T17:30:23.250Z
Comments.Text: I couldn't get  ">1" to work, but I did get this to work:
  
  `SELECT contact_id, group_id,COUNT(contact_id) FROM civicrm_group_contact_cache GROUP BY contact_id ORDER BY COUNT(contact_id) DESC;`
  
  which showed me the contacts in more than 1 smart group, so then it was easy to track down which smart group had the same filter in twice. Thanks for pointing the way @Coleman
Comments.UserDisplayName: Steve Middleton
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-09-01T21:40:46.967Z
Comments.Text: Cool. Just FYI that cache gets cleared regularly so you can't assume it always contains all contacts for every group.
Comments.UserDisplayName: Coleman
Tags: <smartgroups>
Title: List or report on number of smart groups a contact is in
ThreadText: # List or report on number of smart groups a contact is in
  
  (Asked by: username_0 on 2024-08-29)
  
  Is there anyway (using SearchKit or any other way) I can display a list of contacts who are in more than 1 smart group? I have 160,000 contacts and I need to make sure that my contacts are only in 1 smart group. I cannot see a way to do this.
  
  **Tags:** smartgroups
  
  > **Comments:**
  > **username_1**: How many smart groups do you have? If 2, then it'd be easy enough to search for contacts in both groups. If > 2... that's trickier.  
  > **username_0**: I have 24 Smart Groups, with about 9000 contacts spread across these groups. I’m certain I’ve got contacts in more than 1 smart group, which I don’t want.  
  > **username_1**: I think you'd need to write a bit of sql for that. Basically you need to trigger all smart groups to rebuild, then `SELECT contact_id FROM civicrm_group_contact_cache WHERE COUNT(contact_id) > 1 GROUP_BY contact_id;`  
  > **username_0**: I couldn't get  ">1" to work, but I did get this to work:  `SELECT contact_id, group_id,COUNT(contact_id) FROM civicrm_group_contact_cache GROUP BY contact_id ORDER BY COUNT(contact_id) DESC;`  which showed me the contacts in more than 1 smart group, so then it was easy to track down which smart group had the same filter in twice. Thanks for pointing the way @username_1  
  > **username_1**: Cool. Just FYI that cache gets cleared regularly so you can't assume it always contains all contacts for every group.  
  
  ---
  
  ## 0 answers
  
  