ERROR 42803 (grouping_error)

Questions / Help
, , ,
1

I am trying to add these two fields from db inserted_at and updated_at to my result using the following query but I get this error

** (Postgrex.Error) ERROR 42803 (grouping_error) column "sc0.inserted_at" must appear in the GROUP BY clause or be used in an aggregate function

Query:

def posts_for_author(author_id, prefix, args) do
    group_with = Map.get(args, :agg_with, :post_title)

    sort_params = fetch_sort_params(args)

    posts_subquery =
      Post
      |> put_query_prefix(prefix)
      |> group_by([r], ^group_with)
      |>select([p], %{
        post_views: sum(p.post_views),
        inserted_at: p.inserted_at,
        updated_at: p.updated_at
      })
      |> group_posts(group_with)
      |> where(author_id: ^author_id)

    posts_subquery
    |> subquery()
    |> select_merge([p], %{
      total_post_views_in_percentage: p.post_views / type(over(sum(p.post_views)), :float)
    })
    |> order_by(^sort_params)
    |> limit(3)
    |> Repo.all()
  end

defp group_posts(query, :post_title) do
    query |> select_merge([p], %{post_title: p.post_title})
  end

Adding inserted_at and updated_at to group_by clause returns with wrong values for post_views and total_post_views_in_percentage.

Any suggestion to resolve this?

Thanks in advance!

2

Let’s say there are two posts with the same :post_title and different inserted_at and updated_at fields. What value do you expect for inserted_at and updated_at?
You MUST apply an aggregate function to these fields. Try to start with min, max or array_agg.

1 Like