I have table with owners,cars,price. In single query (with usage of over and partition by) i need to get one column with share of value of car in all cars of owner(one owner can have multiple cars) and share of value of car in all cars. I did something like this
price::decimal/sum(price)OVER(PARTITION BY owners.id),
price::decimal/sum(price)OVER(PARTITION BY SUM(price))
First one is working perfectly, but i do not know how to do the second one. SUM is not working because we have to point out to column, but how to get summed value of all cars in this case?
I just have for instance 3 cars, every costs 10$. There are 2 owners A and B. A has 2 cars. So i need to get
Im just curious can i do it without any subquery, just only with Partition By
This is one way:
select
round((price::decimal/sum(price) over (partition by id)),4) * 100 || ' %' as share_of_owner
, round((price::decimal/sum(price) over()), 4) * 100 || ' %' as share_of_cars
from mytable
group by id, cars, price
I have used round
where I have rounded to 4 decimals. You can change that to your needs.
RESULT: