title: MySQL SUM Query with Joins and Union All
body: <p>Can anybody tell me why the second LEFT JOIN in the query below (previous_spend) is returning double the amount it should?</p>
  
  <p>It returns the correct amount if I remove the first LEFT JOIN, so I assume it's doubling the result due to the first LEFT JOIN, but I'm not sure how to rewrite the query to avoid that.</p>
  
  <p>Any help would be much appreciated.</p>
  
  <p>Update: I've created a slightly easier to understand version here: Here's a live example: <a href="http://sqlfiddle.com/#!9/6a9358/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/6a9358/1</a> - as you can see 'current_spend' should return £300.</p>
  
  <pre><code>SELECT 
      COALESCE(SUM(current_spend.total_spend), 0) AS total_spend, 
      COALESCE(SUM(previous_spend.total_previous_spend), 0) AS total_previous_spend, 
      COALESCE(SUM(current_spend.total_spend), 0) - COALESCE(SUM(previous_spend.total_previous_spend), 0) AS total_spend_diff,
      100 * (COALESCE(SUM(current_spend.total_spend), 0) - COALESCE(SUM(previous_spend.total_previous_spend), 0)) / COALESCE(SUM(previous_spend.total_previous_spend), 0) AS total_spend_diff_perc
  FROM customer_scheme
  
  LEFT JOIN (
      SELECT SUM(spend_1 + spend_2) AS total_spend, user_id
      FROM customer_spend
      WHERE customer_spend.spend_year = '2017'
      GROUP BY user_id
   UNION ALL
      SELECT SUM(spend_1 + spend_2 + spend_3 + spend_4 + spend_5 + spend_6 + spend_7 + spend_8 + spend_9 + spend_10 + spend_11 + spend_12) AS total_spend, user_id
      FROM customer_spend
      WHERE customer_spend.spend_year = '2018'
      GROUP BY user_id
   UNION ALL
      SELECT SUM(spend_1 + spend_2 + spend_3 + spend_4 + spend_5 + spend_6 + spend_7 + spend_8 + spend_9 + spend_10 + spend_11 + spend_12) AS total_spend, user_id
      FROM customer_spend
      WHERE customer_spend.spend_year = '2019'
      GROUP BY user_id
   UNION ALL
      SELECT SUM(spend_1 + spend_2) AS total_spend, user_id
      FROM customer_spend
      WHERE customer_spend.spend_year = '2020'
      GROUP BY user_id
  ) as current_spend
      ON current_spend.user_id = customer_scheme.user_id
  
  LEFT JOIN
  (
      SELECT SUM(spend_1 + spend_2) AS total_previous_spend, user_id
      FROM customer_spend
      WHERE customer_spend.spend_year = '2013'
      GROUP BY user_id
   UNION ALL
      SELECT SUM(spend_1 + spend_2 + spend_3 + spend_4 + spend_5 + spend_6 + spend_7 + spend_8 + spend_9 + spend_10 + spend_11 + spend_12) AS total_previous_spend, user_id
      FROM customer_spend
      WHERE customer_spend.spend_year = '2014'
      GROUP BY user_id
   UNION ALL
      SELECT SUM(spend_1 + spend_2 + spend_3 + spend_4 + spend_5 + spend_6 + spend_7 + spend_8 + spend_9 + spend_10 + spend_11 + spend_12) AS total_previous_spend, user_id
      FROM customer_spend
      WHERE customer_spend.spend_year = '2015'
      GROUP BY user_id
   UNION ALL
      SELECT SUM(spend_1 + spend_2) AS total_previous_spend, user_id
      FROM customer_spend
      WHERE customer_spend.spend_year = '2016'
      GROUP BY user_id
  
  ) as previous_spend
      ON previous_spend.user_id = customer_scheme.user_id
  
  LEFT JOIN user
      ON customer_scheme.user_id = user.user_id
  
  WHERE customer_scheme.scheme_id = 36
  
  AND customer_scheme.customer_scheme_access = 'Yes'
  AND user.user_deleted_at IS NULL
  AND user_type = 'Customer'
  AND user.user_status IN (1)
  
  ORDER BY total_spend_diff DESC
  </code></pre>