MySQL Problem While Summarizing Sale Data From Two Separate Table-CodePudding

Thank you very much in advance for your support. I am learning SQL and generating some data to play with but I am stuck on a simple thing. The table in the screenshot below shows some part of my sales20 table which shows the amount of sales each employee achieved in 2020 with region and date information.

Table sales20

I have the same table for 2021. What I want to do is; I want to bring employee name, region, total sale that employee achieved in 2020 and 2021. I have written the following query but it does not bring the correct figures. It brings a lot higher than it should be. The important point is that the employees in 2020 and 2021 are not the same. For example, there is a new employee in 2021, which means he should also be listed but 2020 column should be empty for that employee.

My query:

SELECT sales20.staff, 
       sales20.region,
       SUM(sales20.amount) AS Total_20,
       SUM(sales21.amount) AS Total_21
  FROM sales20
  JOIN sales21 ON sales20.staff = sales21.staff
 GROUP BY staff, region

Partial screenshot of the result:

Result:

Could you please tell me what am I doing wrong?

CodePudding user response：

SELECT sales20.staff,
       sales20.region,
       SUM(sales20.amount) AS Total_20,
       SUM(sales21.amount) AS Total_21
FROM sales20
  JOIN sales21
    ON sales20.staff = sales21.staff
   AND sales20.region = sales21.region
GROUP BY staff,
         region

You need to join the 2 tables based on the staff and region that will make one-to-one join. If you are joining based on only staff then it makes one-to-many join and hence you will get corrupted output.

Another approach would be to combine the data from 2 tables and then perform the aggregation on top of that. This should give you accurate results.

WITH combined_data AS
(
  SELECT staff,
         region,
         saledate,
         amount
  FROM sales20
  UNION ALL
  SELECT staff,
         region,
         saledate,
         amount
  FROM sales21
)
SELECT staff,
       region,
       SUM(CASE WHEN year(saledate) = 2020 THEN amount ELSE 0 END) AS Total_20,
       SUM(CASE WHEN year(saledate) = 2021 THEN amount ELSE 0 END) AS Total_21
FROM combined_data
GROUP BY staff,
         region

CodePudding user response：

You are joining both the tables while you need the union of them -

SELECT staff, 
       region,
       SUM(IF(YEAR(saledate) = '2020',amount,0) AS Total_20,
       SUM(IF(YEAR(saledate) = '2021',amount,0) AS Total_21,
  FROM (SELECT staff, region, amount, saledate
          FROM sales20
         UNION ALL
        SELECT staff, region, amount, saledate
          FROM sales21)
 GROUP BY staff, region;