Clever Geek Handbook

How can I use the SUM function in Oracle SQL to get the below result?

I have a SQL programming question based on Sum Function.Supposing I have the following table.

       ID   Values 

        1     20
        1     30
        1     100
        2     10
        2     1
        2     12
        3     45
        3     66

How to calculate the sum of values ​​related to IDs and add them to a new column. I would also like to group it by ID. For example:

           ID  Values  Total_Value

            1    20       150
            1    30       150
            1    100      150
            2    10       23
            2    1        23 
            2    12       23
            3    45       111
            3    66       111

Any suggestions would be appreciated. Thank!

+3


source to share


4 answers


it's easy to do this with a window function:



select id, 
       value,
       sum(value) over (partition by id) as total_value_for_id
from the_table
order by id;
+5


source


Use analytical functions. Here's what they do there:



select id, value, sum(value) over (partition by id) as totalvalue
from table t
order by id;
+2


source


As I understand from your question, the sum is equal to the sum of the values ​​for those lines with the same ID. For all lines with Id 1, the sum is 150, and for 2 - 23.

So, you need a sub-query that will sum the values ​​by id, and after that you can join these two tables by id.

Select table.*, sumtable.Sum
From table
Join (Select Sum(Values) As Sum, ID From table Group By ID) sumtable 
       On table.ID = sumtable.ID
0


source


You can achieve this either through an internal selection (where you will calculate the sum) or by creating a function and using it in your select statement. The function must take an identifier as a parameter and return SUM.

-1


source






More articles:


All Articles