title: Active Record join, SUM and group_by on votes given
body: <p>I'm using rails 4.2 and ruby 2.2</p>
  
  <p>I have the following three models</p>
  
  <p>Entrant (Devise User)</p>
  
  <pre><code>Entrant.column_names
  =&gt; ["id", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "sign_in_count", "current_sign_in_at", "last_sign_in_at", "current_sign_in_ip", "last_sign_in_ip", "created_at", "updated_at", "confirmation_token", "confirmed_at", "confirmation_sent_at", "unconfirmed_email", "entrant_name"]
  
  class Entrant &lt; ActiveRecord::Base
     has_many :entries
     has_many :votes, :through =&gt; :entries
  end
  </code></pre>
  
  <p>Entry</p>
  
  <pre><code>Entry.column_names
   =&gt; ["id", "entrant_id", "title_of_work", "price", "created_at", "updated_at", "media_file_name", "media_content_type", "media_file_size", "media_updated_at", "paid", "height", "width", "type_of_media"]
  
  class Entry &lt; ActiveRecord::Base
      belongs_to :entrant
      has_many :votes
      has_many :entrants, :through =&gt; :votes
  end
  </code></pre>
  
  <p>Vote</p>
  
  <pre><code>Vote.column_names
  =&gt; ["id", "entry_id", "judge_id", "score", "created_at", "updated_at"]
  
  class Vote &lt; ActiveRecord::Base
      belongs_to :entry
      belongs_to :judge
  end
  </code></pre>
  
  <p>I need a query that will return the following table. I've tried starting with Entrant and joining Votes but nothing has worked. </p>
  
  <p><img src="https://i.stack.imgur.com/3MgHd.png" alt="enter image description here"></p>
  
  <p>The query needs to pull out all entries, and sum the votes.score field and order by votes.score but it also needs to group by entry so that the entries are not duplicated. </p>
  
  <p>This is as close as I have got, but it doesnt work. </p>
  
  <pre><code>@entries = Entrant.joins(:entries).joins(:votes).select('entrants.*, entries.*, SUM(votes.score) as total_votes').distinct.group('entrants.id, entries.id, votes.score')
  </code></pre>