3. UNION ALL and GROUP BY


#1


3. Union All


I do not understand what the 'GROUP BY 1' at the end means. I recall group by from the 'learn sql' but I fail to see what it means here. Could one explain?. Thank You.



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;


#2

This refers to column 1, without regard for the name of the column.


#3

Thank you. Does that mean we are finding the average price for orders that have the same id in the two different tables? And that when we say GROUP BY 1 that means find the average based on column 1(id)? Thanks for the help, I have been quite confused since I left 'Learn SQL' and moved on to "SQL Table Transformation"

The exercise asks us:

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

Correct solution 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;

#4

As I understand, yes. It is the average of the two combined tables at each id.


#5

Thank you and I appreciate your time


#6

Thank you, I had the same question. It seems to me that many of the problems in the SQL module do not provide all of the information necessary to get the correct solution. Very frustrating!


#7

yes,but why when you dont type group by 1 the whole code goes error?? why is necessary to write group by at the end?


#8

in example before this(that neezim showed and the one im confused of) its almost the same problem and code,but you dont have group by at the end.Here it is "SELECT count(*) FROM (
SELECT id, sale_price FROM order_items
UNION ALL
SELECT id, sale_price FROM order_items_historic) as a;"


#9

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