> ## 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 Giving Queries

Start here to learn the fundamentals of querying your church's giving data. Each example builds on the previous one, helping you gain confidence with SQL.

<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 Giving module live in the `planning_center` schema. Always prefix table names with `planning_center.` in your queries.

✅ CORRECT: `SELECT * FROM planning_center.giving_donations`
❌ INCORRECT: `SELECT * FROM giving_donations`

### Row Level Security (RLS)

Row Level Security automatically scopes results by:

* **tenant\_organization\_id** – only your organization's data
* **system\_status** – only active records by default

**Do not add these filters yourself**—RLS already enforces them and redundant predicates can slow queries or hide data you expect to see:

* ❌ `WHERE tenant_organization_id = 1`
* ❌ `WHERE system_status = 'active'`

Focus on donation-specific filters (date ranges, refunded status, funds) while trusting RLS to handle tenancy and system status.

## Table of Contents

* [Viewing Recent Donations](#viewing-recent-donations)
* [Finding Donors](#finding-donors)
* [Working with Funds](#working-with-funds)
* [Date-Based Queries](#date-based-queries)
* [Payment Methods](#payment-methods)
* [Basic Aggregations](#basic-aggregations)

## Viewing Recent Donations

### See Your Latest Donations

```sql theme={null}
-- View the 20 most recent donations
SELECT 
    donation_id,
    amount_cents / 100.0 as amount,  -- Convert cents to dollars
    amount_currency,
    payment_method,
    received_at,
    refunded  -- true if this donation was refunded
FROM planning_center.giving_donations
WHERE received_at IS NOT NULL  -- Only completed donations
ORDER BY received_at DESC
LIMIT 20;
```

### Filter by Donation Amount

```sql theme={null}
-- Find all donations over $500
SELECT 
    donation_id,
    amount_cents / 100.0 as amount,
    payment_method,
    received_at
FROM planning_center.giving_donations
WHERE amount_cents >= 50000  -- $500 in cents
  AND received_at IS NOT NULL
  AND refunded = false  -- Exclude refunded donations
ORDER BY amount_cents DESC;
```

## Finding Donors

### List All Active Donors

```sql theme={null}
-- Get all people who have given
SELECT 
    person_id,
    first_name,
    last_name,
    donor_number
FROM planning_center.giving_people
ORDER BY last_name, first_name;
```

### Search for a Specific Donor

```sql theme={null}
-- Find donors by name
SELECT 
    person_id,
    first_name,
    last_name,
    donor_number
FROM planning_center.giving_people
WHERE LOWER(last_name) LIKE '%smith%'  -- Case-insensitive search
   OR LOWER(first_name) LIKE '%john%'
ORDER BY last_name, first_name;
```

### Connect Donations to Donors

This query shows how to link donations with donor information using the relationship table:

```sql theme={null}
-- See donations with donor names
SELECT 
    d.donation_id,
    d.amount_cents / 100.0 as amount,
    d.received_at,
    p.first_name,
    p.last_name,
    p.donor_number
FROM planning_center.giving_donations d
-- Join through the relationship table
JOIN planning_center.giving_donation_relationships dr 
    ON d.donation_id = dr.donation_id
    AND dr.relationship_type = 'Person'  -- Specify we want the Person relationship
-- Join to the people table
JOIN planning_center.giving_people p 
    ON dr.relationship_id = p.person_id
WHERE d.received_at >= CURRENT_DATE - INTERVAL '30 days'  -- Last 30 days
ORDER BY d.received_at DESC
LIMIT 50;
```

## Working with Funds

### List All Available Funds

```sql theme={null}
-- See all your church's funds
SELECT 
    fund_id,
    name,
    description,
    is_default,  -- true for your general/default fund
    visibility   -- 'everywhere', 'admin_only', etc.
FROM planning_center.giving_funds
WHERE visibility = 'everywhere'  -- Only publicly visible funds
ORDER BY name;
```

### See How Donations Are Designated

```sql theme={null}
-- View designations (how donations are allocated to funds)
SELECT 
    des.designation_id,
    des.amount_cents / 100.0 as amount,
    f.name as fund_name,
    f.description as fund_description
FROM planning_center.giving_designations des
JOIN planning_center.giving_funds f 
    ON des.fund_id = f.fund_id
ORDER BY des.amount_cents DESC
LIMIT 100;
```

### Connect Donations to Their Fund Designations

```sql theme={null}
-- See how each donation was designated to funds
SELECT 
    d.donation_id,
    d.amount_cents / 100.0 as total_donation,
    des.amount_cents / 100.0 as designated_amount,
    f.name as fund_name
FROM planning_center.giving_donations d
-- Join to designation relationships
JOIN planning_center.giving_designation_relationships desr
    ON d.donation_id = desr.designation_id  
    AND desr.relationship_type = 'Donation'
-- Join to designations
JOIN planning_center.giving_designations des
    ON desr.relationship_id = des.designation_id
-- Join to funds
JOIN planning_center.giving_funds f
    ON des.fund_id = f.fund_id
WHERE d.received_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY d.received_at DESC;
```

## Date-Based Queries

### Donations This Month

```sql theme={null}
-- All donations received this month
SELECT 
    COUNT(*) as donation_count,
    SUM(amount_cents) / 100.0 as total_amount
FROM planning_center.giving_donations
WHERE received_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND received_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
  AND refunded = false;
```

### Donations by Week

```sql theme={null}
-- Weekly giving for the last 8 weeks
SELECT 
    DATE_TRUNC('week', received_at) as week_starting,
    COUNT(*) as donation_count,
    SUM(amount_cents) / 100.0 as total_amount,
    AVG(amount_cents) / 100.0 as average_donation
FROM planning_center.giving_donations
WHERE received_at >= CURRENT_DATE - INTERVAL '8 weeks'
  AND refunded = false
GROUP BY DATE_TRUNC('week', received_at)
ORDER BY week_starting DESC;
```

### Year-to-Date Giving

```sql theme={null}
-- Total giving for the current year
SELECT 
    COUNT(DISTINCT donation_id) as total_donations,
    SUM(amount_cents) / 100.0 as total_given,
    AVG(amount_cents) / 100.0 as average_donation,
    MAX(amount_cents) / 100.0 as largest_donation
FROM planning_center.giving_donations
WHERE received_at >= DATE_TRUNC('year', CURRENT_DATE)
  AND received_at < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year'
  AND refunded = false;
```

## Payment Methods

### Breakdown by Payment Method

```sql theme={null}
-- See how people prefer to give
SELECT 
    payment_method,
    COUNT(*) as donation_count,
    SUM(amount_cents) / 100.0 as total_amount,
    AVG(amount_cents) / 100.0 as average_amount
FROM planning_center.giving_donations
WHERE received_at >= CURRENT_DATE - INTERVAL '90 days'
  AND refunded = false
GROUP BY payment_method
ORDER BY total_amount DESC;
```

### Online vs Check Giving

```sql theme={null}
-- Compare electronic vs check donations
SELECT 
    CASE 
        WHEN payment_method = 'check' THEN 'Check'
        WHEN payment_method IN ('card', 'ach', 'paypal') THEN 'Electronic'
        ELSE 'Other'
    END as payment_type,
    COUNT(*) as donation_count,
    SUM(amount_cents) / 100.0 as total_amount
FROM planning_center.giving_donations
WHERE received_at >= CURRENT_DATE - INTERVAL '30 days'
  AND refunded = false
GROUP BY payment_type
ORDER BY total_amount DESC;
```

## Basic Aggregations

### Daily Giving Summary

```sql theme={null}
-- Daily totals for the last 14 days
SELECT 
    DATE(received_at) as donation_date,
    COUNT(*) as num_donations,
    COUNT(DISTINCT dr.relationship_id) as unique_donors,
    SUM(d.amount_cents) / 100.0 as total_amount,
    MIN(d.amount_cents) / 100.0 as smallest_donation,
    MAX(d.amount_cents) / 100.0 as largest_donation
FROM planning_center.giving_donations d
LEFT JOIN planning_center.giving_donation_relationships dr
    ON d.donation_id = dr.donation_id
    AND dr.relationship_type = 'Person'
WHERE d.received_at >= CURRENT_DATE - INTERVAL '14 days'
  AND d.refunded = false
GROUP BY DATE(d.received_at)
ORDER BY donation_date DESC;
```

### Top Donors This Month (Anonymous)

```sql theme={null}
-- Top 10 giving amounts this month (no names for privacy)
SELECT 
    dr.relationship_id as donor_id,  -- Anonymous ID
    COUNT(*) as donation_count,
    SUM(d.amount_cents) / 100.0 as total_given
FROM planning_center.giving_donations d
JOIN planning_center.giving_donation_relationships dr
    ON d.donation_id = dr.donation_id
    AND dr.relationship_type = 'Person'
WHERE d.received_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND d.refunded = false
GROUP BY dr.relationship_id
ORDER BY total_given DESC
LIMIT 10;
```

### Fund Performance Summary

```sql theme={null}
-- How much has been given to each fund this year
SELECT 
    f.name as fund_name,
    COUNT(DISTINCT des.designation_id) as num_designations,
    SUM(des.amount_cents) / 100.0 as total_designated
FROM planning_center.giving_designations des
JOIN planning_center.giving_funds f 
    ON des.fund_id = f.fund_id
-- Get the donation date through relationships
JOIN planning_center.giving_designation_relationships desr
    ON des.designation_id = desr.designation_id
    AND desr.relationship_type = 'Donation'
JOIN planning_center.giving_donations d
    ON desr.relationship_id = d.donation_id
WHERE d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
  AND d.refunded = false
GROUP BY f.fund_id, f.name
ORDER BY total_designated DESC;
```

## Tips for Writing Queries

### 1. Always Convert Cents to Dollars

```sql theme={null}
amount_cents / 100.0 as amount  -- The .0 ensures decimal result
```

### 2. Filter Out Refunded Donations

```sql theme={null}
WHERE refunded = false  -- Unless you specifically want to include refunds
```

### 3. Use received\_at for Timing

* `received_at` = When the donation was actually received
* `created_at` = When it was entered into the system
* `completed_at` = When the transaction completed (may be NULL)

### 4. Handle NULL Values

```sql theme={null}
WHERE received_at IS NOT NULL  -- Only completed donations
COALESCE(amount_cents, 0)      -- Replace NULL with 0
```

### 5. Case-Insensitive Searches

```sql theme={null}
WHERE LOWER(last_name) LIKE '%smith%'  -- Finds Smith, SMITH, smith, etc.
```

## Next Steps

Ready for more complex queries? Check out:

* [Advanced Queries](/planning-center/giving/advanced-queries) - Multi-table joins, subqueries, and complex aggregations
* [Reporting Examples](/planning-center/giving/reporting-examples) - Complete reports you can use immediately

## Common Issues & Solutions

### Issue: No results when joining tables

**Solution**: Check that you're using the correct relationship\_type in your join conditions.

### Issue: Amounts look too large

**Solution**: Remember to divide cents by 100.0 to get dollars.

### Issue: Missing recent donations

**Solution**: Check your WHERE clause - you might be filtering by created\_at instead of received\_at.

### Issue: Duplicate results

**Solution**: You might be missing a DISTINCT or GROUP BY clause, or joining incorrectly through relationship tables.