[SOLVED] Can't SUM DISTINCT values in Ruby on Rails

Can't SUM DISTINCT values in Ruby on Rails

I have 4 tables: Users, Workouts, Exercises, and Results. A "User" posts "Results" for "Exercises" which are linked to a single "Workout". But when the user posts results, since there are multiple exercises, results for one workout can be linked with a unique "post_id". I would like to know how many total minutes a user exercised based on how many "post_ids" they provided which can be linked to the "Workouts" table where a "workout_duration" column shows how many minutes each workout lasts. Here is some sample data, where in this case the workout (workout_id=1) has two exercises and has a workout_duration of 1 minute.

Results:

user_id| workout_id| post_id| exercise_id| number_of_reps|
-------+-----------+--------+------------+---------------+
    123|         1 |       1|          1 |             18|
    123|         1 |       1|          2 |             29|      
    123|         1 |       2|          1 |             15|
    123|         1 |       2|          2 |             30|
    123|         1 |       3|          1 |             20|
    123|         1 |       3|          2 |             28|
-------+-----------+--------+------------+---------------+

Workouts:

workout_id| workout_duration|
----------+-----------------+
         1|                1|

I tried to retrieve the total number of minutes based on the query below, but it is returning a sum of 6 when I want it to return a value of 3...I think this is because the SUM is not taking into account DISTINCT post_ids...rather it is just summing all post_ids.

@user = User.find(current_user)
@total_minutes = @user.results.includes(:workout).select(:post_id).distinct.sum(:workout_duration)

I have searched high and low for solutions to no avail...any ideas?

EDIT: Here is the generated SQL from the query above:

SELECT DISTINCT SUM(workout_duration) 
FROM "results" 
LEFT OUTER JOIN "workouts" ON "workouts"."id" = "results"."workout_id" 
WHERE "results"."user_id" = ?  [["user_id", 123]]

Solution

I solved this by using raw SQL:

 @minute_total = ActiveRecord::Base.connection.execute(minute_query)[0][0]

private
def minute_query
"SELECT SUM(workout_duration) 
FROM (SELECT DISTINCT(results.post_id), results.user_id, workouts.workout_duration 
FROM results LEFT OUTER JOIN workouts ON results.workout_id = workouts.id   
WHERE results.user_id = #{@user.id})"
end