0

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
Kadson Farias
  • 9
  • 1
  • Your try looks correct; Only small mistake; Can you please run below query and check `SELECT Id,Value, (SELECT SUM(Value) FROM Table V2 WHERE V2.Id= V.Id GROUP BY ID) as sum FROM Table v;`; – Tushar Jan 21 '23 at 13:36

4 Answers4

3

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.

lemon
  • 14,875
  • 6
  • 18
  • 38
  • 1
    This will raise ORA-00923: FROM keyword not found where expected error -becouse it needs table or alias to be attached to an * if you want it to be a part of the selection list that has some more columns... @lemon – d r Jan 21 '23 at 16:41
  • 1
    +1 An analytic function is almost certainly the best way to solve this problem. This query will likely be faster and less prone to bugs than other solutions, since the table is only read once. (Although the concept of windowing can be a bit tricky to understand at first.) – Jon Heller Jan 22 '23 at 04:07
1

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;

enter image description here

Tushar
  • 3,527
  • 9
  • 27
  • 49
0

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

SelVazi
  • 10,028
  • 2
  • 13
  • 29
0

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;
  1. GROUP BY IDRNC is a mistake and should be GROUP BY ID
  2. you should give an alias to a sum column ...
  3. 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
d r
  • 3,848
  • 2
  • 4
  • 15