3. UNION ALL and GROUP BY

<PLEASE USE THIS TEMPLATE TO HELP YOU CREATE A GREAT POST!>

<Below this line, add a link to the EXACT exercise that you are stuck at.>
3. Union All

<Below this line, in what way does your code behave incorrectly? Include ALL error messages.>
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.

<In this course, it often helps to include a screenshot of your whole web browser – that lets everyone see what you see. If you wish to include a screenshot, add it below this line.>

<If you wish to copy/paste in your code, you can use this next section. This will allow others to copy/paste your code for testing – something that they won’t be able to do with just a screenshot.>

```

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;

<do not remove the three backticks above>

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

2 Likes

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;
1 Like

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

2 Likes

Thank you and I appreciate your time

1 Like

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!

8 Likes

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?

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;”

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