I have two tables: A and B.
Table A has the following set-up:
| ID | date | location | sales |
|----|------------|----------|---------|
| 1 | 2022-01-01 | 1 | 10000 |
| 2 | 2022-01-02 | 1 | 10000 |
| 3 | 2022-01-04 | 1 | 10000 |
|... | .... | 2 | .... |
So 2022-01-03 has no matching row.
And table B has the following set-up:
| ID | date | location | budget |
|----|------------|----------|------------|
| 1 | 2022-01-01 | 1 | 10000 |
| 2 | 2022-01-03 | 1 | 10000 |
| 3 | 2022-01-04 | 1 | 10000 |
|... | .... | 2 | .... |
So 2022-01-02 has no matching row.
I am trying to join the tables together to get the following output
|location | sales | budget |
|---------|----------|---------|
|1 | 30000 | 30000 |
|2 | ... | ... |
So I can group it on location and get | location ABC | sales 123 | budget 123 |, which is a sum of all the dates grouped but also joined the 2 tables together on date and location.
The query I have now:
SELECT SUM(A.sales) AS sales, A.restaurant
FROM A
LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
WHERE A.date between ? AND ?
GROUP BY A.location
UNION
SELECT SUM(B.budget) AS budget, B.restaurant
FROM A
RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
WHERE B.date between ? AND ?
GROUP BY B.restaurant
I've tried different types of joins and unions and ended with a query as suggested in this Answer to mimic a full outer join. However, with this query I get the following output:
| location | column |
|----------|---------|
| 1 | 30000 |
| 2 | ... |
| 3 | ... |
| 1 | 30000 |
| 2 | ... |
| 3 | ... |
...
These sums are correct but not in 2 separate columns 'sales' and 'budget'.
Is there a way can I achieve this?
