Skip to main content
Parable
Open Parable

Basic Planning Center Groups Queries

Foundational SQL for Planning Center Groups: list active groups, count members, and see who joined a group within a given date range.

Start your journey into Groups data with these foundational queries. Each example builds your SQL skills while providing immediate value for your ministry.

Query Customization Required

These example queries demonstrate common patterns but may require adjustments to match your specific database schema and field names. Test thoroughly in your environment before use.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Groups module live in the planning_center schema. Always prefix table names with planning_center. when querying.

✅ CORRECT: SELECT * FROM planning_center.groups_groups ❌ INCORRECT: SELECT * FROM groups_groups

Row Level Security (RLS)

Row Level Security automatically enforces:

  • tenant_organization_id – results scoped to your organization
  • system_status – only active records returned by default

Skip manual filters for these columns—RLS already applies them and extra predicates can suppress data or hurt performance:

  • ❌ WHERE tenant_organization_id = 1
  • ❌ WHERE system_status = 'active'

Keep your filters focused on ministry context (archived groups, roles, schedules) while RLS handles tenant isolation.

Table of Contents

Viewing Groups

List All Active Groups

-- See all your active groups with basic information
SELECT 
    group_id,
    name,
    description,
    memberships_count,
    schedule,
    location_type_preference
FROM planning_center.groups_groups
WHERE archived_at IS NULL  -- Only active groups
ORDER BY name;

Find Groups by Name

-- Search for groups containing specific words
SELECT 
    group_id,
    name,
    description,
    memberships_count,
    created_at
FROM planning_center.groups_groups
WHERE LOWER(name) LIKE '%youth%'  -- Case-insensitive search
   OR LOWER(description) LIKE '%youth%'
   AND archived_at IS NULL
ORDER BY memberships_count DESC;

Groups by Size

-- Find large groups that might need to split
SELECT 
    group_id,
    name,
    memberships_count,
    schedule,
    location_type_preference
FROM planning_center.groups_groups
WHERE memberships_count > 12  -- Groups larger than 12
  AND archived_at IS NULL
ORDER BY memberships_count DESC;

Recently Created Groups

-- See groups created in the last 90 days
SELECT 
    group_id,
    name,
    description,
    memberships_count,
    created_at
FROM planning_center.groups_groups
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
  AND archived_at IS NULL
ORDER BY created_at DESC;

Finding Members

List All People in Groups

-- Get all people registered in the Groups system
SELECT 
    person_id,
    permissions,
    created_at
FROM planning_center.groups_people
ORDER BY created_at DESC
LIMIT 100;

People with Leadership Permissions

-- Find people who can lead groups
SELECT 
    person_id,
    permissions,
    created_at
FROM planning_center.groups_people
WHERE permissions IN ('leader', 'administrator')  -- Adjust based on your permission types
ORDER BY created_at DESC;

Understanding Memberships

View All Memberships

-- See how people are connected to groups
SELECT 
    m.membership_id,
    mr_person.relationship_id as person_id,
    mr_group.relationship_id as group_id,
    m.role,
    m.joined_at
FROM planning_center.groups_memberships m
LEFT JOIN planning_center.groups_memberships_relationships mr_person
    ON m.membership_id = mr_person.membership_id AND mr_person.relationship_type = 'Person'
LEFT JOIN planning_center.groups_memberships_relationships mr_group
    ON m.membership_id = mr_group.membership_id AND mr_group.relationship_type = 'Group'
ORDER BY m.joined_at DESC
LIMIT 50;

Find Leaders vs Members

-- Count leaders and members across all groups
SELECT 
    role,
    COUNT(*) as count
FROM planning_center.groups_memberships
GROUP BY role
ORDER BY count DESC;

Recent Group Joins

-- People who joined groups in the last 30 days
SELECT 
    m.membership_id,
    mr_person.relationship_id as person_id,
    mr_group.relationship_id as group_id,
    m.role,
    m.joined_at
FROM planning_center.groups_memberships m
LEFT JOIN planning_center.groups_memberships_relationships mr_person
    ON m.membership_id = mr_person.membership_id AND mr_person.relationship_type = 'Person'
LEFT JOIN planning_center.groups_memberships_relationships mr_group
    ON m.membership_id = mr_group.membership_id AND mr_group.relationship_type = 'Group'
WHERE m.joined_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY m.joined_at DESC;

Connect Members to Groups

-- See members with their group names
SELECT 
    m.membership_id,
    mr_person.relationship_id as person_id,
    m.role,
    m.joined_at,
    g.name as group_name,
    g.schedule,
    g.memberships_count
FROM planning_center.groups_memberships m
JOIN planning_center.groups_memberships_relationships mr_group
    ON m.membership_id = mr_group.membership_id AND mr_group.relationship_type = 'Group'
JOIN planning_center.groups_groups g 
    ON mr_group.relationship_id = g.group_id
LEFT JOIN planning_center.groups_memberships_relationships mr_person
    ON m.membership_id = mr_person.membership_id AND mr_person.relationship_type = 'Person'
WHERE g.archived_at IS NULL
ORDER BY m.joined_at DESC
LIMIT 100;

Working with Events

List Upcoming Events

-- See events happening in the next 30 days
SELECT 
    event_id,
    name,
    description,
    starts_at,
    ends_at,
    location_type_preference,
    virtual_location_url
FROM planning_center.groups_events
WHERE starts_at >= CURRENT_TIMESTAMP
  AND starts_at <= CURRENT_TIMESTAMP + INTERVAL '30 days'
  AND canceled = false
ORDER BY starts_at;

Find Canceled Events

-- Review events that were canceled
SELECT 
    event_id,
    name,
    starts_at,
    canceled_at,
    description
FROM planning_center.groups_events
WHERE canceled = true
  AND canceled_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY canceled_at DESC;

Multi-Day Events

-- Find retreats, camps, or multi-day events
SELECT 
    event_id,
    name,
    description,
    starts_at,
    ends_at,
    (ends_at - starts_at) as duration
FROM planning_center.groups_events
WHERE multi_day = true
  AND starts_at >= CURRENT_DATE
ORDER BY starts_at;

Events with Virtual Options

-- Find events with online participation
SELECT 
    event_id,
    name,
    starts_at,
    location_type_preference,
    virtual_location_url
FROM planning_center.groups_events
WHERE virtual_location_url IS NOT NULL
  AND starts_at >= CURRENT_TIMESTAMP
ORDER BY starts_at
LIMIT 20;

Tracking Attendance

View Recent Attendance Records

-- See who's been attending events
SELECT 
    attendance_id,
    attended,
    system_created_at
FROM planning_center.groups_attendances
WHERE system_created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY system_created_at DESC
LIMIT 50;

Attendance Rate Summary

-- Calculate overall attendance rate
SELECT 
    COUNT(*) FILTER (WHERE attended = true) as attended_count,
    COUNT(*) FILTER (WHERE attended = false) as absent_count,
    COUNT(*) as total_records,
    ROUND(
        COUNT(*) FILTER (WHERE attended = true)::numeric / 
        NULLIF(COUNT(*), 0)::numeric * 100, 
        1
    ) as attendance_rate
FROM planning_center.groups_attendances
WHERE system_created_at >= CURRENT_DATE - INTERVAL '30 days';

Date-Based Queries

Groups Created This Year

-- All groups started this year
SELECT 
    group_id,
    name,
    memberships_count,
    created_at
FROM planning_center.groups_groups
WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
  AND archived_at IS NULL
ORDER BY created_at DESC;

Weekly Event Schedule

-- Events for the current week
SELECT 
    event_id,
    name,
    starts_at,
    DATE_PART('dow', starts_at) as day_of_week,
    TO_CHAR(starts_at, 'FMDay') as day_name,
    TO_CHAR(starts_at, 'HH24:MI') as start_time
FROM planning_center.groups_events
WHERE starts_at >= DATE_TRUNC('week', CURRENT_DATE)
  AND starts_at < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week'
  AND canceled = false
ORDER BY starts_at;

Monthly Membership Growth

-- Track new memberships by month
SELECT 
    DATE_TRUNC('month', joined_at) as month,
    COUNT(*) as new_memberships
FROM planning_center.groups_memberships
WHERE joined_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', joined_at)
ORDER BY month DESC;

Basic Aggregations

Group Statistics

-- Overview of your groups ministry
SELECT 
    COUNT(*) as total_groups,
    COUNT(*) FILTER (WHERE archived_at IS NULL) as active_groups,
    COUNT(*) FILTER (WHERE archived_at IS NOT NULL) as archived_groups,
    AVG(memberships_count) FILTER (WHERE archived_at IS NULL) as avg_group_size,
    MAX(memberships_count) FILTER (WHERE archived_at IS NULL) as largest_group,
    MIN(memberships_count) FILTER (WHERE archived_at IS NULL AND memberships_count > 0) as smallest_group
FROM planning_center.groups_groups;

Groups by Meeting Type

-- How groups prefer to meet
SELECT 
    location_type_preference,
    COUNT(*) as group_count,
    AVG(memberships_count) as avg_size
FROM planning_center.groups_groups
WHERE archived_at IS NULL
GROUP BY location_type_preference
ORDER BY group_count DESC;

Membership Role Distribution

-- Understand your leader to member ratio
SELECT 
    g.group_id,
    g.name,
    COUNT(*) FILTER (WHERE m.role = 'leader') as leaders,
    COUNT(*) FILTER (WHERE m.role = 'member') as members,
    COUNT(*) as total_members
FROM planning_center.groups_groups g
JOIN planning_center.groups_memberships_relationships mr
    ON mr.relationship_id = g.group_id AND mr.relationship_type = 'Group'
JOIN planning_center.groups_memberships m ON m.membership_id = mr.membership_id
WHERE g.archived_at IS NULL
GROUP BY g.group_id, g.name
HAVING COUNT(*) > 0
ORDER BY total_members DESC
LIMIT 20;

Event Frequency by Day

-- Which days have the most events
SELECT 
    TO_CHAR(starts_at, 'FMDay') as day_name,
    DATE_PART('dow', starts_at) as day_number,
    COUNT(*) as event_count
FROM planning_center.groups_events
WHERE starts_at >= CURRENT_DATE - INTERVAL '90 days'
  AND canceled = false
GROUP BY day_name, day_number
ORDER BY day_number;

Groups Without Recent Events

-- Find potentially inactive groups (no events in 60 days)
SELECT 
    g.group_id,
    g.name,
    g.memberships_count,
    MAX(e.starts_at) as last_event_date
FROM planning_center.groups_groups g
LEFT JOIN planning_center.groups_events_relationships er 
    ON er.relationship_type = 'Group' AND er.relationship_id = g.group_id
LEFT JOIN planning_center.groups_events e 
    ON e.event_id = er.event_id
WHERE g.archived_at IS NULL
GROUP BY g.group_id, g.name, g.memberships_count
HAVING MAX(e.starts_at) < CURRENT_DATE - INTERVAL '60 days'
    OR MAX(e.starts_at) IS NULL
ORDER BY last_event_date NULLS FIRST;

Tips for Writing Queries

1. Check for Active Groups

Archived groups keep a non-null archived_at, so filter it out for active groups.

SELECT group_id, name
FROM planning_center.groups_groups
WHERE archived_at IS NULL;

2. Handle NULL Values Properly

Use IS NULL / IS NOT NULL — never = NULL.

SELECT event_id, name
FROM planning_center.groups_events
WHERE virtual_location_url IS NOT NULL  -- has a virtual option
  AND canceled_at IS NULL;              -- not canceled

3. Use Date Functions

CURRENT_DATE is today, CURRENT_TIMESTAMP is now, and DATE_TRUNC snaps a timestamp to the start of a period.

SELECT
    DATE_TRUNC('month', starts_at) as month,
    COUNT(*) as events
FROM planning_center.groups_events
WHERE starts_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY DATE_TRUNC('month', starts_at)
ORDER BY month;

4. Case-Insensitive Searches

Lowercase the column before comparing so casing does not matter.

SELECT group_id, name
FROM planning_center.groups_groups
WHERE LOWER(name) LIKE '%youth%';

5. Aggregate with Filters

FILTER (WHERE ...) counts only the rows matching a condition, without a CASE.

SELECT
    COUNT(*) as total_events,
    COUNT(*) FILTER (WHERE canceled = true) as canceled_events
FROM planning_center.groups_events;

Common Issues & Solutions

Issue: No results when joining tables

Solution: Make sure you're using the correct join columns and that data exists in both tables.

Issue: Duplicate results

Solution: You might need to use DISTINCT or check your join conditions.

Issue: Date comparisons not working

Solution: Ensure you're using the correct date/timestamp format and comparison operators.

Issue: Group counts don't match

Solution: Check if you're filtering for active groups (archived_at IS NULL).

Next Steps

Ready to connect data across tables? Continue with:

Planning Center Groups Data Model

Complete reference for Planning Center Groups tables in Parable: groups, memberships, group types, events, attendance, and how they relate.

Advanced Planning Center Groups Queries

Advanced Groups SQL combining multiple tables and window functions to analyze group health, attendance consistency, and member retention over time.

On this page

Query RequirementsSchema PrefixRow Level Security (RLS)Table of ContentsViewing GroupsList All Active GroupsFind Groups by NameGroups by SizeRecently Created GroupsFinding MembersList All People in GroupsPeople with Leadership PermissionsUnderstanding MembershipsView All MembershipsFind Leaders vs MembersRecent Group JoinsConnect Members to GroupsWorking with EventsList Upcoming EventsFind Canceled EventsMulti-Day EventsEvents with Virtual OptionsTracking AttendanceView Recent Attendance RecordsAttendance Rate SummaryDate-Based QueriesGroups Created This YearWeekly Event ScheduleMonthly Membership GrowthBasic AggregationsGroup StatisticsGroups by Meeting TypeMembership Role DistributionEvent Frequency by DayGroups Without Recent EventsTips for Writing Queries1. Check for Active Groups2. Handle NULL Values Properly3. Use Date Functions4. Case-Insensitive Searches5. Aggregate with FiltersCommon Issues & SolutionsIssue: No results when joining tablesIssue: Duplicate resultsIssue: Date comparisons not workingIssue: Group counts don't matchNext Steps