3. Union ALL WHY!


#1

First I code this :
SELECT id, avg(a.sale_price) FROM (
SELECT id, sale_price FROM order_items
UNION ALL
SELECT id, sale_price FROM order_items_historic) AS a;


it doesn't work.
So, I gave up and the answer is :

SELECT id, avg(a.sale_price) FROM (
SELECT id, sale_price FROM order_items
UNION ALL
SELECT id, sale_price FROM order_items_historic) AS a
GROUP BY 1;


I just wonder that it return the same result but why my code doesn't work.
Thank you for explaination:grin:


#2

It doesn't exactly give the same answer.

In UNION ALL, there may be same order ID between two tables, which would have same prices.
If we display all of them, it would be duplicated here.
Therefore, we averaged them. If there is no duplicate, it would return the ID and sale price itself, since the average of one is itself.
If there is duplicate between two tables, since the sale price is the same, it would be added and then divided by two, returning the same sale price.


#3