How to sum the values of an associated table with multiple joins without duplicates

I have a Rails 3 app, using mysql, with 3 models: Order, Payment, Refund. Payment and Refund both have a belongs_to association with Order. Order has_many payments and refunds respectively.

I am simply trying to get totals for the payments and refunds, for the orders within a given time range, broken down by day.

The closest I have been able to come is with the following code:

    scope = Order.in_a_completed_status.purchased_between(from, to)

    scope = scope.joins(<<-SQL.strip_heredoc)
      INNER JOIN payments ON payments.order_id = orders.id AND payments.status <> "Removed"
    SQL

    scope = scope.joins(<<-SQL.strip_heredoc)
      LEFT JOIN refunds ON refunds.order_id = orders.id
    SQL

    scope = scope.select(<<-SQL.strip_heredoc)
      COALESCE(sum(payments.amount), 0) as value,
      COALESCE(sum(refunds.amount), 0) as total_refund,
      DATE(CONVERT_TZ(orders.created_on, '+00:00', '#{Time.current.formatted_offset}')) AS date
    SQL

    scope = scope.group("date")

The problem with the above is that it will add a refund amount multiple times if there are multiple payments for the order. So if we have the following tables

Orders
____________________________
| id | created_on          |
| 34 | June 4th, 2020      |
____________________________

Payments
______________________________________
| id | order_id | amount | status   |
| 2  |   34     | 500    | complete |
| 3  |   34     | 200    | complete |
______________________________________

Refunds
___________________________
| id | order_id | amount  |
| 6  |   34     | 300     |
|____|__________|_________|

The total_refund that results for June 4th will be 600 instead of 300 because the refund gets added once for each payment, instead of just once. I have tried adding in a DISTINCT clause and various other things but I haven't gotten anything to work. Any help or insights would be greatly appreciated. Thanks in advance.