I can't sum total amount of transactions in Money

[Teslima02]

What am trying to achieve

I want to be able to query total transaction with the sum function and money composite type

query =
  __MODULE__
  |> select([transaction], %{
    total_amount: type(sum(transaction.amount), transaction.amount),
  })

Repo.all(query)

The problem
I am using elixir money library and each time I ran the query I got the following error

** (Postgrex.Error) ERROR 42883 (undefined_function) function sum(money_with_currency) does not exist
     
         query: SELECT sum(t0."amount")::money_with_currency FROM "transactions" AS t0 WHERE (t0."business_id" = $1)
     
         hint: No function matches the given name and argument types. You might need to add explicit type casts.

I searched for the issue but solutions I found it not working for me, I checked similar issue on elixir forum.

https://elixirforum.com/t/ecto-how-to-sum-a-field-with-money-datatype/26736/4

Proposed solution but is not working

[epd]

Just encountered this. You can use fragment/2 to get the sum:

total_amount: sum(fragment("(?).amount", field(transaction, :amount)))

This roughly translates to the following Postgres query:

select sum((transactions.amount).amount) from transactions;