Posted by Yuanyuan, March 1, 2022, 9:21 p.m.
#111 Having
with category_revenue as(
select PC.product_category_id, O.product_id, sum(qty)quantities,
sum(qty * unit_price_usd)as revenue,
dense_rank() over (partition by product_category_id order by sum(qty * unit_price_usd) desc)as ranking
FROM product_category PC
inner join orders O
on O.product_id = PC.product_id
group by PC.product_category_id, O.product_id
having quantities >= 2)
select product_category_id, product_id, revenue, ranking
from category_revenue
where ranking = 2
Can I add quantities--the new definedd aggregation column name after having?
select PC.product_category_id, O.product_id, sum(qty)quantities,
sum(qty * unit_price_usd)as revenue,
dense_rank() over (partition by product_category_id order by sum(qty * unit_price_usd) desc)as ranking
FROM product_category PC
inner join orders O
on O.product_id = PC.product_id
group by PC.product_category_id, O.product_id
having quantities >= 2)
select product_category_id, product_id, revenue, ranking
from category_revenue
where ranking = 2
Can I add quantities--the new definedd aggregation column name after having?
