I Join several tables on the principle of left join . And the result is several columns that match for user_id and it turns out that the sum is considered twice.

 SELECT s.id AS s__id, s.user_id AS s__user_id, d.account_currency_id AS currency_id, d.platform AS d__2, SUM(d.amount_with_commission) AS sum FROM sf_guard_user_profile s LEFT JOIN sf_guard_user s2 ON s.user_id = s2.id LEFT JOIN user_promo_source u ON s2.id = u.user_id LEFT JOIN user_summary u2 ON s2.id = u2.user_id LEFT JOIN sf_guard_user_profile_accounts s3 ON ( ( s2.id = s3.user_id AND s3.is_remove = 0 ) ) LEFT JOIN deposit_request d ON ( ( d.account_id = s3.id AND ( d.status_id = 5 OR d.status_id = 8 OR d.withdraw_id IS NOT NULL ) ) ) LEFT JOIN sf_guard_user_calls s4 ON s4.user_id = s2.id WHERE ( s.lang = 'en' AND ( u2.start_reg_date >= "2016-09-01 00:00:00" AND u2.start_reg_date <= "2016-09-30 23:59:59" ) AND s4.created_at >= '2016-09-01 00:00:00' AND s.is_test_profile = 0 AND ( NOT (d.amount_with_commission IS NULL) ) ) GROUP BY d.account_currency_id, d.platform ORDER BY d__0 DESC

DISTINCT at the beginning of SQL did not help, how to remedy the situation, How to write SQL so that for each user the amount is counted once?

  • @Mike Even if grouped by s__id duplicate columns still exist, and the amount will be recalculated for the repeating column. - user216109
  • You didn’t give your entire request, so I have nothing to write an answer for (for the answer, you need to remake the joines, put them into each other). see my answer to a similar question in English. stackoverflow.com/questions/39148729/… - Mike
  • @Mike I updated the question and added the whole SQL. - user216109
  • @Mike I already have such a complex query, resource-intensive. It won't pull Subquery. I don’t want to group it into php. I thought there is some option - user216109
  • @Mike Okay, thanks, I'll try through the subquery. - user216109

1 answer 1

According to the results of the correspondence, the request was brought to this:

 SELECT s.id AS s__id, s.user_id AS s__user_id, d.account_currency_id AS currency_id, d.platform AS d__2, SUM(d.amount_with_commission) AS sum FROM sf_guard_user_profile s JOIN user_summary u2 ON u2.user_id = s.user_id JOIN sf_guard_user_calls s4 ON s4.user_id = s.user_id JOIN sf_guard_user_profile_accounts s3 ON s3.user_id = s.user_id AND s3.is_remove = 0 JOIN deposit_request d ON d.account_id = s3.id AND (d.status_id in(5,8) OR d.withdraw_id IS NOT NULL) WHERE s.lang = 'en' AND u2.start_reg_date >= "2016-09-01 00:00:00" AND u2.start_reg_date <= "2016-09-30 23:59:59" AND s.user_id in(select user_id from sf_guard_user_calls where created_at >= '2016-09-01 00:00:00') AND s.is_test_profile = 0 AND d.amount_with_commission IS NOT NULL GROUP BY d.account_currency_id, d.platform ORDER BY d__0 DESC

The table that multiplies the records (sf_guard_user_calls) is in the subquery, as it was needed only for filtering by date. Also, unnecessary LEFT JOINs are replaced by regular JOINs, which can make life easier for the optimizer.

The filtering condition can also be written in the form:

 AND EXISTS(select 1 from sf_guard_user_calls s4 where created_at >= '2016-09-01 00:00:00' and s4.user_id = s.user_id)

In rare cases, it may be faster than IN.