Aggregate Functions

The extension provides aggregate functions that preserve currency type safety.

Same-Currency Aggregates

These functions require all values to have the same currency:

monetary_sum

Sums monetary values:

monetary_avg

Calculates the average:

monetary_min / monetary_max

Finds minimum and maximum values:

Currency Mismatch Errors

Aggregating different currencies throws an error:

To aggregate mixed currencies, use monetary_sum_converted.

Converting Aggregates

monetary_sum_converted

Sums values after converting to a target currency:

monetary_sum_converted(value, target_currency, rate) → monetary

For dynamic rates from an exchange table:

-- Load rates
CREATE TABLE rates AS SELECT * FROM ecb_exchange_rates();

-- Join to get rates for conversion
WITH prices AS (
    SELECT '100 EUR'::monetary AS price, 'EUR' AS curr UNION ALL
    SELECT '100 USD'::monetary, 'USD' UNION ALL
    SELECT '100 GBP'::monetary, 'GBP'
)
SELECT monetary_sum_converted(
    price,
    'EUR',
    COALESCE(r.rate, 1.0)
) AS total_eur
FROM prices p
LEFT JOIN rates r ON r.currency_to = p.curr;

Summary

Function	Same Currency	Description
`monetary_sum(m)`	Required	Sum values
`monetary_avg(m)`	Required	Average values
`monetary_min(m)`	Required	Find minimum
`monetary_max(m)`	Required	Find maximum
`monetary_sum_converted(m, target, rate)`	No	Sum with conversion

Group By

Aggregate functions work with GROUP BY: