Ecto query sum avg

Questions / Help
,
1

Hello, all! I have a noob question about ecto.query.
Table:

Fuzz
fizz_id	fizz_buzz
2	         1000
2	         1000
2	         1000
3	         3000
3	         3000
4	         1000

Take a look:

      Repo.all(
        from(n in Fuzz,
          group_by: n.fizz_id,
          select: avg(n.fizz_buzz)
        )
      )

The result that I get is [1000, 3000, 1000]. Can I get with query the result that I really want. In that example [1667]?

2

have you tried removing the group_by？

2 Likes
3

Why do you have group_by there if you want to have average from all of them?

2 Likes
4

I want to get only one representation with fizz_id, I was trying with :distinct but this also need group by

5

I want to have avarage only for one representation wih fizz_id

6

I don’t really know what you want. Maybe something like this:

      Repo.all(
        from(n in Fuzz,
          where: n.fizz_id == 2,
          select: avg(n.fizz_buzz)
        )

That would return 1000.
Your original Repo.all returns the avg for all fizz_ids.

7

Then I don’t understand what is wrong because 1000, 3000, 1000 are averages per fizz_id?

fizz_id	fizz_buzz
2	         1000
2	         1000
2	         1000
= 3000 divided by 3 is 1000

3	         3000
3	         3000
= 6000 divided by 2 is 3000

4	         1000
= 1000 divided by 1 is 1000
1 Like
8

yeah they are avarages and I want to get avarage from this avarages :D, I am asking if for that is some query method, if not I can do it manually

9

Ah, ok. Have you tried Ecot.Query.subquery?

1 Like
10

Ok so you need average of averages. I haven’t used Ecto a lot and I’m not sure it supports select from select because it has to support range of databases. Inner select would be this query and outer select would return average of it. You might be able to solve this with a fragment of raw SQL. Or if there aren’t that many averages returned by that Ecto query you can always just use ä Elixir code to calculate that last average.