How do i select all columns, plus the result of the sum-CodePudding

I have this select:

"Select * from table" that return:

Id	Value
1	1
1	1
2	10
2	10

My goal is create a sum from each Value group by id like this:

Id	Value	Sum
1	1	2
1	1	2
2	10	20
2	10	20

I Have tried ways like:

SELECT Id,Value, (SELECT SUM(Value) FROM Table V2 WHERE V2.Id= V.Id GROUP BY IDRNC ) FROM Table v;

But the is not grouping by id.

Id	Value	Sum
1	1	1
1	1	1
2	10	10
2	10	10

CodePudding user response：

Your attempt looks correct. Can you try the below query :

It works for me :

SELECT Id, Value, 
(SELECT SUM(Value) FROM Table V2 WHERE V2.Id= V.Id GROUP BY ID) as sum 
FROM Table v;

CodePudding user response：

Aggregation aggregates rows, reducing the number of records in the output. In this case you want to apply the result of a computation to each of your records, task carried out by the corresponding window function.

SELECT table.*, SUM(Value) OVER(PARTITION BY Id) AS sum_
FROM table

Check the demo here.

CodePudding user response：

You can do it using inner join to join with selection grouped by id :

select t.*, sum
from _table t
inner join (
  select id, sum(Value) as sum
  from _table
  group by id
) as s on s.id = t.id

You can check it here

CodePudding user response：

Your select is ok if you adjust it just a little:

SELECT Id,Value, (SELECT SUM(Value) FROM Table V2 WHERE V2.Id= V.Id GROUP BY IDRNC ) FROM Table v;

GROUP BY IDRNC is a mistake and should be GROUP BY ID
you should give an alias to a sum column ...
subquery selecting the sum does not have to have self table alias to be compared with outer query that has one (this is not a mistake - works either way)
Test:

WITH
    a_table (ID, VALUE) AS
        (
            Select 1, 1 From Dual Union All
            Select 1, 1 From Dual Union All
            Select 2, 10 From Dual Union All
            Select 2, 10 From Dual
        )
SELECT ID, VALUE, (SELECT SUM(VALUE) FROM a_table WHERE ID = v.ID GROUP BY ID) "ID_SUM" FROM a_table v;

        ID      VALUE     ID_SUM
---------- ---------- ----------
         1          1          2 
         1          1          2 
         2         10         20 
         2         10         20