Union ALL and group by


Here is a subquery to find the average sale price over both order_items and order_items_historic tables.

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;

Here if i don't use GROUP BY, i get only one value in the rsult. But on using group by, i get the complete set of values. Why is it so, why GROUP BY is deciding the number of rows here?


GROUP BY forces the solution table to apply the average operation to each distinct id, otherwise, it collapses the table to a scalar output, because an average is a single-output operation.


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