> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getparable.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Basic queries

# 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.

<Card title="Query Customization Required" icon="circle-exclamation" color="#FFA500" horizontal>
  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.
</Card>

## 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](#viewing-groups)
* [Finding Members](#finding-members)
* [Understanding Memberships](#understanding-memberships)
* [Working with Events](#working-with-events)
* [Tracking Attendance](#tracking-attendance)
* [Date-Based Queries](#date-based-queries)
* [Basic Aggregations](#basic-aggregations)

## Viewing Groups

### List All Active Groups

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
WHERE archived_at IS NULL  -- Active groups only
```

### 2. Handle NULL Values Properly

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
WHERE LOWER(name) LIKE '%search_term%'  -- Converts to lowercase for searching
```

### 5. Aggregate with Filters

```sql theme={null}
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](/planning-center/groups/data-model) - Learn to join groups, members, and events
* [Advanced Queries](/planning-center/groups/advanced-queries) - Complex analysis and reporting
* [Reporting Examples](/planning-center/groups/reporting-examples) - Real-world ministry scenarios and solutions