CODEWITHSUNDEEP
ruby-on-railsrubysum
appleLover
appleLover

Reputation: 15701

Rails Query - Sum Results

Score.where(period: ["q1", "q2"]).pluck(:game_id, :home_score, :away_score)

This query returns results like this:

[[57927, 26, 19], [57927, 28, 23], 
[57928, 12, 21], [57928, 17, 25], 
[57929, 28, 15], [57929, 24, 20]]

How can I sum the :home_score and :away_score results of the same :game_id, to get this

[[57927, 54, 42], 
[57928, 29, 46], 
[57929, 52, 35]]

Upvotes: 1

Views: 149

Answers (2)

Ismael
Ismael

Reputation: 16730

You need to use the group clause.

Score.where(period: ["q1", "q2"]).group(:game_id)
    .select('game_id, sum(home_score) as home_score_sum, sum(away_score) as away_score_sum')
    .all.map {|s| [s.game_id, s.home_score_sum, s.away_score_sum]}

Upvotes: 1

BroiSatse
BroiSatse

Reputation: 44725

Assuming game_id is a foreign key mapping to Game object, try:

Game.joins(:scores).select("#{Game.table_name}.id, SUM(#{Game.table_name}.home_score) AS home_total, SUM(#{Game.table_name}.away_score) AS total_away").group("#{Game.table_name}.id").pluck[:id, :home_total, :away_total]

Couldn't test it, but it should work with single database query.

Upvotes: 3

Related Questions