Common Queries

A collection of useful BQL queries for everyday financial reporting.

Balance Queries

Current Balances

-- All account balances
SELECT account, sum(position) AS balance
GROUP BY account ORDER BY account

-- Specific account
SELECT account, sum(position) AS balance
WHERE account ~ "Assets:Bank"
GROUP BY account

-- Top-level summary
SELECT root(account, 1) AS type, sum(position) AS balance
GROUP BY type

Balance at Date

-- Balances as of a specific date
SELECT account, sum(position) AS balance
WHERE date <= 2024-06-30
GROUP BY account

Expense Analysis

Monthly Expenses

-- Total expenses by month
SELECT year(date) AS year, month(date) AS month, sum(cost(position)) AS total
WHERE account ~ "Expenses"
GROUP BY year, month
ORDER BY year, month

Expenses by Category

-- Breakdown by expense category
SELECT root(account, 2) AS category, sum(cost(position)) AS total
WHERE account ~ "Expenses"
GROUP BY category
ORDER BY total DESC

Year-over-Year Comparison

-- Compare years with separate queries
-- 2023 expenses:
SELECT root(account, 2) AS category, sum(cost(position)) AS total
WHERE account ~ "Expenses" AND year(date) = 2023
GROUP BY category ORDER BY total DESC

-- 2024 expenses:
SELECT root(account, 2) AS category, sum(cost(position)) AS total
WHERE account ~ "Expenses" AND year(date) = 2024
GROUP BY category ORDER BY total DESC

Income Analysis

Income by Source

SELECT root(account, 2) AS source, sum(cost(position)) AS total
WHERE account ~ "Income"
GROUP BY source
ORDER BY total

Monthly Income vs Expenses

-- Monthly income
SELECT year(date) AS year, month(date) AS month, sum(cost(position)) AS income
WHERE account ~ "Income"
GROUP BY year, month ORDER BY year, month

-- Monthly expenses
SELECT year(date) AS year, month(date) AS month, sum(cost(position)) AS expenses
WHERE account ~ "Expenses"
GROUP BY year, month ORDER BY year, month

Transaction Queries

Recent Transactions

-- Last 20 transactions
SELECT date, narration, account, position
ORDER BY date DESC
LIMIT 20

Search by Payee

SELECT date, payee, narration, account, position
WHERE payee ~ "Amazon"
ORDER BY date DESC

Large Transactions

-- Find transactions over $500 (filter results manually or use report command)
SELECT date, payee, narration, account, cost(position) AS amount
WHERE account ~ "Expenses"
ORDER BY date DESC

Transactions with Tag

SELECT date, narration, account, position
WHERE "vacation" IN tags
ORDER BY date

Investment Queries

Holdings with Cost Basis

SELECT account, currency, sum(units(position)) AS units,
       sum(cost(position)) AS cost_basis
WHERE account ~ "Assets:Brokerage"
GROUP BY account, currency

Realized Gains

SELECT year(date) AS year, sum(cost(position)) AS gains
WHERE account ~ "Income:CapitalGains"
GROUP BY year

Net Worth

Current Net Worth

SELECT sum(cost(position)) AS net_worth
WHERE account ~ "Assets" OR account ~ "Liabilities"

Net Worth by Account Type

SELECT root(account, 1) AS type, sum(cost(position)) AS total
WHERE account ~ "Assets" OR account ~ "Liabilities"
GROUP BY type

Utility Queries

List All Accounts

SELECT DISTINCT account
ORDER BY account

List All Payees

SELECT DISTINCT payee
WHERE payee IS NOT NULL
ORDER BY payee

Account Activity

-- Find accounts with transactions in a date range
SELECT DISTINCT account
WHERE date >= 2024-01-01 AND date <= 2024-03-31
ORDER BY account

Tips

Save Common Queries

Create shell aliases for frequently used queries:

alias expenses='rledger query ledger.beancount "SELECT root(account, 2), sum(cost(position)) WHERE account ~ \"Expenses\" GROUP BY 1 ORDER BY 2 DESC"'

Output to CSV

rledger query -f csv ledger.beancount "SELECT ..." > report.csv

Combine with Other Tools

# Pipe to jq for JSON processing
rledger query -f json ledger.beancount "SELECT ..." | jq '.rows[]'

# Use with datamash for quick stats
rledger query -f csv ledger.beancount "SELECT ..." | datamash sum 2

See Also

• query command - Full query command reference
• BQL Reference - Complete BQL syntax