Thank you very much in advance for your support. I'm learning SQL and generating some data to play with, but I'm stuck on one simple thing. The table in the screenshot below shows a portion of my sales20 table that shows the sales achieved by each employee in 2020 along with region and date information.
Table Sales 20
I have the same table from 2021. What I want to do is; I want to provide the employee name, region, total sales achieved by the employee in 2020 and 2021. I wrote the following query but it is not giving the correct numbers. It brings something higher than it deserves. The important point is that employees in 2020 and 2021 are different. For example, there is a new employee in 2021, which means he should be listed as well, but the 2020 column for that employee should be empty.
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 results:
result:
Can you tell me what I'm doing wrong?
When you need to merge two tables, you are joining them -
You need to join 2 tables based on person and region for a one to one join. If you join based only on employees then it will do a one to many join so you will get corrupted output.
Another approach is to merge the data from the two tables and then perform aggregation on this basis. This should give you accurate results.