Aggregates (PostgreSQL)

The PostgreSQL extension provides aggregate functions that enforce currency consistency within each aggregation group.

SUM

Sum monetary values within a currency:

-- Basic sum
SELECT SUM(amount)
FROM transactions
WHERE monetary_currency(amount) = 'EUR';

-- Sum by currency
SELECT monetary_currency(amount) as currency,
       SUM(amount) as total
FROM transactions
GROUP BY monetary_currency(amount);

Currency Mismatch

Attempting to sum different currencies throws an error:

-- This will error if transactions contain mixed currencies
SELECT SUM(amount) FROM transactions;
-- ERROR: cannot sum EUR and USD: currency mismatch in aggregate

Solution: Group by currency or filter first:

-- Option 1: Group by currency
SELECT monetary_currency(amount), SUM(amount)
FROM transactions
GROUP BY monetary_currency(amount);

-- Option 2: Filter to single currency
SELECT SUM(amount)
FROM transactions
WHERE monetary_currency(amount) = 'EUR';

AVG

Calculate average with proper rounding:

SELECT AVG(amount) as avg_transaction
FROM transactions
WHERE monetary_currency(amount) = 'USD';

The average uses round-half-away-from-zero for consistent results.

MIN / MAX

Find minimum and maximum values:

SELECT
    MIN(amount) as smallest,
    MAX(amount) as largest
FROM transactions
WHERE monetary_currency(amount) = 'EUR';

COUNT

Count works normally (currency-agnostic):

SELECT
    monetary_currency(amount) as currency,
    COUNT(*) as num_transactions,
    SUM(amount) as total
FROM transactions
GROUP BY monetary_currency(amount);

Window Functions

Use aggregates with OVER for running totals:

SELECT
    id,
    created_at,
    amount,
    SUM(amount) OVER (
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM transactions
WHERE monetary_currency(amount) = 'EUR'
ORDER BY created_at;

Practical Examples

Daily Revenue Report

SELECT
    DATE(created_at) as date,
    monetary_currency(amount) as currency,
    COUNT(*) as num_orders,
    SUM(amount) as revenue,
    AVG(amount) as avg_order
FROM orders
GROUP BY DATE(created_at), monetary_currency(amount)
ORDER BY date DESC;

Top Customers by Spend

SELECT
    customer_id,
    monetary_currency(amount) as currency,
    SUM(amount) as total_spent,
    COUNT(*) as num_orders
FROM orders
GROUP BY customer_id, monetary_currency(amount)
ORDER BY monetary_decimal(SUM(amount)) DESC
LIMIT 10;

Monthly Comparison

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) as month,
        SUM(amount) as revenue
    FROM orders
    WHERE monetary_currency(amount) = 'USD'
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_month
FROM monthly
ORDER BY month;

Performance Tips

1. Filter early: Apply currency filters in WHERE, not HAVING
2. Index currency: If frequently grouping by currency, consider a partial index
3. Partition large tables: Partition by currency for separate processing

-- Partial index for EUR transactions
CREATE INDEX idx_orders_eur ON orders(created_at)
WHERE monetary_currency(amount) = 'EUR';

-- Query uses index
SELECT SUM(amount)
FROM orders
WHERE monetary_currency(amount) = 'EUR'
  AND created_at > '2025-01-01';

Aggregate Summary

Function	Description	Currency Check
SUM(monetary)	Sum values	Yes - must match
AVG(monetary)	Average with rounding	Yes - must match
MIN(monetary)	Minimum value	Yes - must match
MAX(monetary)	Maximum value	Yes - must match
COUNT(*)	Count rows	No