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.

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

Viewing Recent Donations
Finding Donors
Working with Funds
Date-Based Queries
Payment Methods
Basic Aggregations

Viewing Recent Donations

See Your Latest Donations

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

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

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

-- 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:

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

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

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

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

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

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

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

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

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

-- 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)

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

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

amount_cents / 100.0 as amount  -- The .0 ensures decimal result

2. Filter Out Refunded Donations

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

WHERE received_at IS NOT NULL  -- Only completed donations
COALESCE(amount_cents, 0)      -- Replace NULL with 0

5. Case-Insensitive Searches

WHERE LOWER(last_name) LIKE '%smith%'  -- Finds Smith, SMITH, smith, etc.

Next Steps

Ready for more complex queries? Check out:

Advanced Queries - Multi-table joins, subqueries, and complex aggregations
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.

​Basic Giving Queries

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Table of Contents

​Viewing Recent Donations

​See Your Latest Donations

​Filter by Donation Amount

​Finding Donors

​List All Active Donors

​Search for a Specific Donor

​Connect Donations to Donors

​Working with Funds

​List All Available Funds

​See How Donations Are Designated

​Connect Donations to Their Fund Designations

​Date-Based Queries

​Donations This Month

​Donations by Week

​Year-to-Date Giving

​Payment Methods

​Breakdown by Payment Method

​Online vs Check Giving

​Basic Aggregations

​Daily Giving Summary

​Top Donors This Month (Anonymous)

​Fund Performance Summary

​Tips for Writing Queries

​1. Always Convert Cents to Dollars

​2. Filter Out Refunded Donations

​3. Use received_at for Timing

​4. Handle NULL Values

​5. Case-Insensitive Searches

​Next Steps

​Common Issues & Solutions

​Issue: No results when joining tables

​Issue: Amounts look too large

​Issue: Missing recent donations

​Issue: Duplicate results