SQL sum grouped by field with all rows

Question

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.

Answer 1

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;

Answer 2

Don't use GROUP BY in the sub-query, make it a co-related sub-query:

SELECT sl1.id, sl1.sale_id, sl1.price, 
       (SELECT sum(sl2.price) FROM sale_lines sl2 where sl2.sale_id = sl.sale_id) as total 
FROM sale_lines sl1