How to dynamically add aggregation to select?

Questions / Help
,
1

I need to dynamically add aggregation to Ecto query, like

# user_specified_aggregation_name can be one of
# "sum", "average", "median", "standard deviation", "variant"
dynamic_aggregation = build_dynamic_aggregation(user_specified_aggregation_name)

from("some_table")
|> select([t], %{foo: t.foo, bar: ^dynamic_aggregation})
|> group_by([t], t.foo)

I just don’t know how to build this dynamic_aggregation.

I tried something like

defp build_dynamic_aggregation("sum") do
  dynamic([d], sum(d.bar))
end

defp build_dynamic_aggregation("median") do
  dynamic(
    [d],
    fragment("percentile_cont (0.5) WITHIN GROUP (ORDER BY ?)", d.bar)
  )
end

but it gives me an error:

dynamic expressions can only be interpolated at the top level of where, having, group_by, order_by, update or a join's on

How can I do this?

2

Use multiple function headers with different select_merge calls instead of using dynamic.

3

It’s not that I need to add multiple aggregations. It’s that I only need one aggregation, but it’s chosen by the users.

4 
defp add_bar_aggregation(query, "sum") do
  select_merge(query, [d], %{bar: sum(d.bar)})
end

defp add_bar_aggregation(query, "median") do
  select_merge(query, [d], %{bar: fragment("percentile_cont (0.5) WITHIN GROUP (ORDER BY ?)", d.bar)})
end
2 Likes
5

Thank you so much. I’ll try it immediately.

6

It works! Thank you so much!