Aggregate multiple dynamically selected columns in Ecto (without group by)

Questions / Help
1

I’m having some trouble figuring out how to solve this query generation problem with Ecto:

I want to sum multiple columns in a table, which I could easily do with this:

from(i in "data",
  select: [
    sum(i.income_a),
    sum(i.income_b),
    sum(i.income_c)
  ]
)

I have a few different sets of columns, like [:income_a, :income_b, :income_c] and [:tax_a, :tax_b], which I want to use to generate a query like the above; something like this:

def make_query(columns) do
  from(i in "data", select: sum(^columns))
end

The idea is to sum each column independently, like in the first query, but I can’t figure out how to do that without specifying the columns explicitly. Is there any way to do this with Ecto? Is there a better approach?

2

You could use Enum.reduce/3 together with Ecto.Query.select_merge/2, like so:

query = from(i in "data", select: %{})
fields = [:income_a, :income_b, :tax_a, :tax_b]
query = 
  Enum.reduce(fields, query, fn field, query -> 
    select_merge(query, %{^field => sum(^field)}) 
  end)

Would that work for you?

1 Like
3

Ah thanks, I had tried using select_merge before without luck, but your comment made me look at it again—here is what ended up working for me:

query = from(i in "data", select: %{})
fields = [:income_a, :income_b]
# fields = [:tax_a, :tax_b]
query = 
  Enum.reduce(fields, query, fn field, query -> 
    select_merge(query, [i], %{^Atom.to_string(field) => sum(field(i, ^field))})
  end)
1 Like