elixir ecto - the right way to get an avarage score from a column

user_100371 0
Viewed 53

what is the right way to get an average number from a column? i have this query:

q = from r in Review,
      where: r.module_id == ^module_id,
      where: r.delete == false,
      select: %{entries: r, stars: avg(r.stars)}

    r = Repo.all(q)

will always get this error:

(grouping_error) column "r0.id" must appear in the GROUP BY clause or be used in an aggregate function

what do that means and how to resolve that?

ecto elixir
1 Answers

One cannot select a result having different dimensions. What do you expect to get in entries when avg aggregates on different reviews?

Consider the following input:

| id | module_id | stars |
|----|-----------|-------| 
|  1 |     1     |   3   |
|  2 |     1     |   5   |
|  3 |     2     |   1   |

OK, for module_id == 1, avg(stars) would be 4. But what should be r then?

That said, you should probably rethink the query itself.

q =
  from r in Review,
  where: r.delete == false,
  where: r.module_id == ^module_id,
  group: r.module_id,
  select: %{module: r.module_id, stars: avg(r.stars)}

or like, depending on your real need.

user_20352620
Related