title: Ecto Elixir: Sum values in preloaded association
body: <p>I have a database which has different songs that can have multiple requests and each request can in part have multiple votes.</p>
  
  <p>I'm trying to build a query that gets all of the info for a request (song information and votes made by users) but I just want to select the important information I need. </p>
  
  <p>I currently have it set up like this </p>
  
  <pre><code>def get_requests_in_zone(zone_id, max_played) do
  query = from request in Request,
    left_join: song in assoc(request, :song),
    where: request.zone_id == ^zone_id and request.times_played &lt;= ^max_played,
    select: request,
    preload: [song: song],
    order_by:  [asc: request.times_played, desc: request.inserted_at]
  Repo.all(query)
   end
  
  def  calculate_rating(request_id)do
  query = from votes in Vote,
    where: votes.request_id == ^request_id,
    select: sum(votes.rating)
  
  Repo.one(query)
  end
  
  def get_requests_in_zone_with_votes(zone_id,max_played) do
  Enum.map(get_requests_in_zone(zone_id,max_played),
    fn(x) -&gt;
      %{title: x.song.title, url: x.song.url, thumbnail: x.song.thumbnail, rating: calculate_rating(x.id)} end)
  end
  </code></pre>
  
  <p>But that makes multiple queries to calculate the rating for each request. I tried building a single query, it was something akin to:</p>
  
  <pre><code>def get_requests_in_zone(zone_id, max_played) do
  query = from song in Song,
    left_join: request in assoc(song, :requests),
    join: votes in assoc(request, :votes), 
  where: request.zone_id == ^zone_id and request.times_played &lt;= ^max_played,
    select: %{title: song.title, url: song.url, thumbnail: song.thumbnail, rating: sum(votes.rating)},
    preload: [requests: {request, votes: votes}],
    order_by:  [asc: request.times_played, desc: request.inserted_at]
  Repo.all(query)
  end
  </code></pre>
  
  <p>But it didn't work... Giving out this error message </p>
  
  <p>** (Ecto.QueryError) field <code>.Song.request</code> in preload is not an association in query:</p>
  
  <p>So I decided to add the complete song in the select by changing the select line to </p>
  
  <pre><code>  select: %{song: song, rating: sum(votes.rating)},
  </code></pre>
  
  <p>And it just said 
       ERROR 42803 (grouping_error): column "s0.id" must appear in the GROUP BY clause or be used in an aggregate function</p>
  
  <p>How could I build a query that retrieves the requests and returns a struct similar to the one used in get_requests_in_zone_with_votes?</p>
  
  <p>Thanks for you time. </p>