Need help with my code - UNION/ALL


#1

Using the same pattern, utilize a subquery to find the average sale price over both order_items and order_items_historic tables.

Below is my code but I still got error message


SELECT AVG(sale_price) FROM (
  SELECT id, sale_price FROM order_items
  UNION ALL
  SELECT id, sale_price FROM order_items_historic) as a;


#2

What error are you getting?


#3

It marked the instruction as wrong, which means I couldn't progress with the exercise. Though no error message was produced per se.


#4

I got the same problem...


#5

were you able to solve it? I am yet to figure it out.


#6

Below is the answer:

SELECT id,avg(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;


#7

Okay, I understood the instructions in a completely different way. Thought that the goal was to find the overall average sale_price of all order_items and order_items_historic, just like the given pattern with the COUNT(*) aggregate function. But the intention of the exercise was to find the average for each individual item...


#8

Agree with you.... I Made the same query as jideogbu proposed at first.... The instructions says clearly: "find the average sale price over both order_items and order_items_historic tables." ... so THE average PRICE is singular, never mention PRICES.


#9

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