OVER PARTITION BY SUM

user_7475686 0
Viewed 301

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

  • A's 1st car 50%(value of A's cars) 33%(value of all cars)
  • A's 2nd car 50%(value of A's cars) 33%(value of all cars)
  • B's 1st car 100%(value of B's cars) 33%(value of all cars)

Im just curious can i do it without any subquery, just only with Partition By

window-functions postgresql sql
1 Answers

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

here is a demo

I have used round where I have rounded to 4 decimals. You can change that to your needs.

RESULT:

enter image description here

user_65650380
Related