How to do absolute sum using Ecto

Phoenix Forum Questions / Help
, ,
#1

I am trying to create ecto query for below sql query
— SQL
select sum(abs(column1)) from Table;

— Ecto

(from r in Table, select: sum(abs(r.column1))|>MyApp.Repo.one()

But I am getting below error while doing so. I checked in Ecto documentation, I didn’t find abs support.
** (Ecto.Query.CompileError)abs(c.vvm01)is not a valid query expression.

#2

Hello and welcome,

Ecto fragment should help You…

https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments

not tested, but something like this maybe.

fragment("sum(abs(?))", r.column1)
2 Likes
#3

One can even do:

sum(fragment("abs(?)", q.col))

If you use abs more often then you can do:

defmacro abs(c), do: fragment("abs(?)", unquote(c))

Or you can use ecto_function and do:

defqueryfunc abs(column)

API of ecto_function will probably change in 2.x to support more Elixir - I am planning supporting some cases and ifs similarly to Nx.

3 Likes