I have this table:
id sale_id price ------------------- 1 1 100 2 1 200 3 2 50 4 3 50
I want this result:
id sale_id price sum(price by sale_id) ------------------------------------------ 1 1 100 300 2 1 200 300 3 2 50 50 4 3 50 50
I tried this:
SELECT id, sale_id, price, (SELECT sum(price) FROM sale_lines GROUP BY sale_id) FROM sale_lines
But get the error that subquery returns different number of rows. How can I do it?
I want all the rows of sale_lines table selecting all fields and adding the sum(price) grouped by sale_id.
Advertisement
Answer
You can use window function :
sum(price) over (partition by sale_id) as sum
If you want sub-query then you need to correlate them :
SELECT sl.id, sl.sale_id, sl.price, (SELECT sum(sll.price) FROM sale_lines sll WHERE sl.sale_id = sll.sale_id ) FROM sale_lines sl;