Union ALL and group by


#1

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?


#2

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.


#3

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