Basic Groups Queries

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.

Viewing Groups
Finding Members
Understanding Memberships
Working with Events
Tracking Attendance
Date-Based Queries
Basic Aggregations

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', 'admin')  -- Adjust based on your permission types
ORDER BY created_at DESC;

Understanding Memberships

View All Memberships

-- See how people are connected to groups
SELECT 
    membership_id,
    person_id,
    group_id,
    role,
    joined_at
FROM planning_center.groups_memberships
ORDER BY 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 
    membership_id,
    person_id,
    group_id,
    role,
    joined_at
FROM planning_center.groups_memberships
WHERE joined_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY joined_at DESC;

Connect Members to Groups

-- See members with their group names
SELECT 
    m.membership_id,
    m.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_groups g 
    ON m.group_id = g.group_id
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,
    created_at
FROM planning_center.groups_attendances
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY 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 / 
        COUNT(*)::numeric * 100, 
        1
    ) as attendance_rate
FROM planning_center.groups_attendances
WHERE 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, 'Day') 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 m ON g.group_id = m.group_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, 'Day') 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_event_relationships er 
    ON g.group_id = er.group_id 
    AND er.relationship_type = 'Group'
LEFT JOIN planning_center.groups_events e 
    ON er.relationship_id = e.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

WHERE archived_at IS NULL  -- Active groups only

2. Handle NULL Values Properly

-- Use IS NULL or IS NOT NULL for comparisons
WHERE virtual_location_url IS NOT NULL  -- Has virtual option
WHERE canceled_at IS NULL  -- Not canceled

3. Use Date Functions

-- Current date/time functions
CURRENT_DATE            -- Today's date
CURRENT_TIMESTAMP       -- Current date and time
DATE_TRUNC('month', date_field)  -- Start of month

4. Case-Insensitive Searches

WHERE LOWER(name) LIKE '%search_term%'  -- Converts to lowercase for searching

5. Aggregate with Filters

COUNT(*) FILTER (WHERE condition)  -- Count only matching rows

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:

Data Model - Learn to join groups, members, and events
Advanced Queries - Complex analysis and reporting
Reporting Examples - Real-world ministry scenarios and solutions

Documentation Index

​Basic Groups Queries

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Table of Contents

​Viewing Groups

​List All Active Groups

​Find Groups by Name

​Groups by Size

​Recently Created Groups

​Finding Members

​List All People in Groups

​People with Leadership Permissions

​Understanding Memberships

​View All Memberships

​Find Leaders vs Members

​Recent Group Joins

​Connect Members to Groups

​Working with Events

​List Upcoming Events

​Find Canceled Events

​Multi-Day Events

​Events with Virtual Options

​Tracking Attendance

​View Recent Attendance Records

​Attendance Rate Summary

​Date-Based Queries

​Groups Created This Year

​Weekly Event Schedule

​Monthly Membership Growth

​Basic Aggregations

​Group Statistics

​Groups by Meeting Type

​Membership Role Distribution

​Event Frequency by Day

​Groups Without Recent Events

​Tips for Writing Queries

​1. Check for Active Groups

​2. Handle NULL Values Properly

​3. Use Date Functions

​4. Case-Insensitive Searches

​5. Aggregate with Filters

​Common Issues & Solutions

​Issue: No results when joining tables

​Issue: Duplicate results

​Issue: Date comparisons not working

​Issue: Group counts don’t match

​Next Steps