Set Operation UNION ALL with AVG Function


#1

I'm working on exercise 3/6 in Set Operations - UNION ALL and here's my code:

SELECT avg(sale_price) FROM (
SELECT sale_price FROM order_items
UNION ALL
SELECT sale_price FROM order_items_historic);

the answer appears as 62.1063499999998

I'm not getting an error, the system is just preventing me from moving forward. Please advise.

Thanks


#2

Read the post titled "3. Union ALL wired error message", I think it may help.


#3

Thank you. It worked. That's weird. I spoke to a DBA at work, and she said they would never use the GROUP BY clause in this instance.

Thanks again.

Keisha


#4

If we also SELECT the id column in this case, and then use GROUP BY 1, isn't that taking the average price of each id? Meaning, there are two items with the same id, but one is in the order_items table and one is in the order_items_historic table, and we are averaging their price. If that is the intention of the question, then fine, it's just a little confusing because I interpreted the question as taking the average of ALL the price points on the two tables combined. So I actually wrote the same query as keishamia.


#5

I agree with you 100% it gives a different result. That could be fine, in fact sort of makes sense if you are thinking real live avg of jeans, t-shirts, etc. Instead of avg of the whole thing. But wasn't that clear specially from the example above that they showed.


#6

Solution

The statement of the question is confusing and should be: Using the same pattern, utilize a subquery to find the average sale price of each item over both order_items and order_items_historic tables.

SELECT id,AVG(sale_price) FROM (
SELECT id, sale_price FROM order_items
UNION ALL
SELECT id, sale_price
FROM order_items_historic
) GROUP BY 1;


#7

I am having trouble with this exercise as well and am totally at a loss as to what to do here is my code SELECT count (*) FROM (
SELECT id, avg(a.sale_price) FROM order_items
UNION ALL
SELECT id, avg(a.sale_price) FROM order_items_historic) as a;

it says Error: near line 1: no such column: a.sale_price

any suggestions


#8

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 tried this and it worked, however, I am at a loss as to how I could have figured it out otherwise


#9

it says Error: near line 1: no such column: a.sale_price

'AS' renames columns or tables in the result - not in the original. Drop 'a' from the nested SELECTS, you can use it at the top.


#10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.