Combining MySQL queries yields incorrect answer

OK, so we have a lot of sales data for each of our clients. I have been easily able to find the query to get the total volume of sales for each sales rep using a simple query:

SELECT  `MerchantAddresses`.`Rep Number` AS `Rep Number`,
          SUM(`RESIDUALS_2010_12`.`Qual Cr Vol` + `RESIDUALS_2010_12`.`Qual Ch Vol`) AS `VOL_2010_12`,
          `Reps`.`First` AS `First`,
          `Reps`.`Last` AS `Last`
FROM     `MerchantAddresses`, `RESIDUALS_2010_12`, `Reps`
WHERE   `RESIDUALS_2010_12`.`MID` = `MerchantAddresses`.`MID` AND
          `Reps`.`ID` = `MerchantAddresses`.`Rep Number`
GROUP BY    `MerchantAddresses`.`Rep Number`
ORDER BY SUM(`RESIDUALS_2010_12`.`Qual Cr Vol` + `RESIDUALS_2010_12`.`Qual Ch Vol`) DESC

This code works totally fine, returning a table grouping total sales by sales rep for a single month. At the moment, we have been running t开发者_如何学JAVAhree separate queries to get sales data for 3 months. I want to combine these three queries into one.

So, I did the following:

SELECT  `MerchantAddresses`.`Rep Number` AS `Rep Number`,
          SUM(`RESIDUALS_2010_12`.`Qual Cr Vol` + `RESIDUALS_2010_12`.`Qual Ch Vol`) AS `VOL_2010_12`,
          SUM(`RESIDUALS_2010_11`.`Qual Cr Vol` + `RESIDUALS_2010_11`.`Qual Ch Vol`) AS `VOL_2010_11`,
          SUM(`RESIDUALS_2010_10`.`Qual Cr Vol` + `RESIDUALS_2010_10`.`Qual Ch Vol`) AS `VOL_2010_10`,
          `Reps`.`First` AS `First`,
          `Reps`.`Last` AS `Last`
FROM     `MerchantAddresses`, `RESIDUALS_2010_12` JOIN ON `RESIDUALS_2010_11` JOIN ON `RESIDUALS_2010_10`, `Reps`
WHERE   `RESIDUALS_2010_12`.`MID` = `MerchantAddresses`.`MID` AND
          `RESIDUALS_2010_11`.`MID` = `MerchantAddresses`.`MID` AND
          `RESIDUALS_2010_10`.`MID` = `MerchantAddresses`.`MID` AND
          `Reps`.`ID` = `MerchantAddresses`.`Rep Number`
GROUP BY  `MerchantAddresses`.`Rep Number`
ORDER BY SUM(`RESIDUALS_2010_12`.`Qual Cr Vol` + `RESIDUALS_2010_12`.`Qual Ch Vol`) DESC

What I have found is that I actually get incorrect values with this query. It works, but the volume value I get for each sales rep is actually too small...

Any ideas? Thanks!

I think you can't just join these monthly tables, because they are not really related in that way. They share the same merchant address, but that's all.

Using UNION ALL, you can combine the results of multiple queries. That way you can get the value of all monthly records in a subquery and sum them in a parent.

I took the liberty of adding some aliasing in your queries.

SELECT
  m.`Rep Number`,
  SUM(m.vol10) as VOL_2010_10,
  SUM(m.vol11) as VOL_2010_11,
  SUM(m.vol12) as VOL_2010_12,
  m.First,
  m.Last
FROM  
  (
  SELECT  
    ma.`Rep Number`,
    r10.`Qual Cr Vol` + r10.`Qual Ch Vol` AS vol10,
    null as vol11,
    null as vol12
  FROM
    MerchantAddresses ma
    INNER JOIN RESIDUALS_2010_10 as r10 on r10.MID = ma.MID
  UNION ALL
  SELECT  
    ma.`Rep Number`,
    null as vol10,
    r11.`Qual Cr Vol` + r11.`Qual Ch Vol` AS vol11,
    null as vol12
  FROM
    MerchantAddresses ma
    INNER JOIN RESIDUALS_2010_11 as r11 on r11.MID = ma.MID
  UNION ALL 
  SELECT  
    ma.`Rep Number`,
    null as vol10,
    null as vol11,
    r12.`Qual Cr Vol` + r12.`Qual Ch Vol` AS vol12
  FROM
    MerchantAddresses ma
    INNER JOIN RESIDUALS_2010_12 as r12 on r12.MID = ma.MID
  ) m
  INNER JOIN Reps r ON r.ID = m.`Rep Number`
GROUP BY
  m.`Rep Number`
ORDER BY 
  SUM(m.vol12) DESC