QUERY

SELECT
  `t`.`id`,
  `t`.`offer_id` AS `Offer_ID`,

  IFNULL(SUM(OtherTransactions.sum_hold), 0) AS sum_hold,
  IFNULL(SUM(OtherTransactions.sum_unhold), 0) AS sum_unhold,
  IFNULL(SUM(OtherTransactions.sum_chargeback), 0) AS sum_chargeback,
  IFNULL(SUM(OtherTransactions.sum_bonus), 0) AS sum_bonus,

  SUM(t.amount) AS sum_payout

FROM `payments_transactions` `t`
  LEFT OUTER JOIN (
    /* В этой же таблице хранятся другие транзакции типов Hold, UnHold, Chargeback, Bonus */
    SELECT id_daily_earning,
      -- выбираем суммы по типу
      SUM(IF(OtherTransactions.TYPE = 'Hold', OtherTransactions.amount, 0)) AS sum_hold,
      SUM(IF(OtherTransactions.TYPE = 'UnHold', OtherTransactions.amount, 0)) AS sum_unhold,
      SUM(IF(OtherTransactions.TYPE = 'Chargeback', OtherTransactions.amount, 0)) AS sum_chargeback,
      SUM(IF(OtherTransactions.TYPE = 'Bonus', OtherTransactions.amount, 0)) AS sum_bonus

    FROM `payments_transactions` OtherTransactions

    WHERE
    (OtherTransactions.TYPE = 'Hold'
      OR OtherTransactions.TYPE = 'UnHold'
      OR OtherTransactions.TYPE = 'Chargeback'
      OR OtherTransactions.TYPE = 'Bonus')
    -- группируем по полю, через которое ссылаются
    GROUP BY OtherTransactions.id_daily_earning

  ) OtherTransactions
  ON OtherTransactions.id_daily_earning = t.id

-- выбираем дневные заработки
WHERE (t.TYPE = "Daily Earning")

GROUP BY t.offer_id

ORDER BY t.id DESC