CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Looking_for_answers
Looking_for_answers

Reputation: 343

How to find the difference of sum between maximum cumulative sum and minimum cumulative sum?

Thank you in advance.

I have a table :

 id||   Subject ||   Marks

 1 ||   English ||   10

 2 ||   Maths   ||   30

 3 ||   History ||   50

 4 ||   English ||   70

 5 ||   Maths   ||   80

 6 ||   History ||   90

 7 ||   English ||   100

 8 ||   Maths   ||   130

 9 ||   History ||   150

I want to find the difference of sum in terms of max cumulative sum and minimum cumulative sum for each subject:

Example

Subject ||   Marks  ||   Cumulative sum    ||  Cumulative diff(max-min)

history ||    50    ||   50                ||

History ||    90    ||   140               || 

History ||    150   ||   290               || (290-50)=240

I am able to get the cumulative sum but unable to find the rest of the values:

 select t1.id,t1.marks,sum(t2.marks) as sum1
 from [NorthWind].[dbo].[Table_1] t1
 inner join [NorthWind].[dbo].[Table_1] t2 on t1.id>=t2.id
 group by t1.id,t1.marks
 order by t1.id

Thank you.

Upvotes: 0

Views: 289

Answers (4)

user7715598
user7715598

Reputation: 

DECLARE @TempData AS TABLE
 (
    Id int,
    Subject varchar(20),
    Marks int
 )
 INSERT INTO @TempData
 VALUES 
( 1, 'English', 10),
( 2, 'Maths',   30),
( 3, 'History', 50),
( 4, 'English', 70),
( 5, 'Maths',   80),
( 6, 'History', 90),
( 7, 'English', 100),
( 8, 'Maths',   130),
( 9, 'History', 150 )

Declare @SelectSubject Varchar(20)='History' --SELECT your subject here like English,Maths,History

SELECT Subject
    ,Marks
    ,RunningTotal AS CumulativeSum
    ,CASE 
        WHEN Rno = 3
            THEN Cumulativediff
        ELSE ''
        END AS Cumulativediff
FROM (
    SELECT *
        ,  (
            MAX(RunningTotal) OVER (ORDER BY Subject)) - (MIN(RunningTotal) OVER (ORDER BY Subject)
            ) AS Cumulativediff
        ,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Rno
    FROM (
        SELECT *
        FROM (
            SELECT ISNULL(Subject, 'GrandTotal') AS Subject
                ,ISNULL(CASt(Marks AS VARCHAR(10)), 'SubTotal') AS Marks
                ,SUM(Marks) OVER (
                    PARTITION BY Subject ORDER BY Id
                    ) AS RunningTotal
            FROM @TempData
            ) Dt
        WHERE dt.Subject = @SelectSubject
        ) Dt2
    ) Final

Result:

Subject Marks   CumulativeSum   Cumulativediff
-----------------------------------------------
History 50       50             0
History 90       140            0
History 150      290            240

Upvotes: 1

Utsav
Utsav

Reputation: 8093

Try this

Rextester sample

with tbl1 as (
select t.*
,sum(marks) over (partition by subject order by id) as cum_sum
,sum(marks) over (partition by subject order by id) 
- min(marks) over (partition by subject order by id) 
    as diff
,row_number() over (partition by subject order by id desc) as rnk
from your_table t
)
select 
id,Subject,Marks,cum_sum
,case when rnk=1 then diff else null end as cum_diff
from tbl1 t
order by subject,id;

Output:

+----+---------+-------+---------+----------+
| id | Subject | Marks | cum_sum | cum_diff |
+----+---------+-------+---------+----------+
|  1 | English |    10 |      10 | NULL     |
|  4 | English |    70 |      80 | NULL     |
|  7 | English |   100 |     180 | 170      |
|  3 | History |    50 |      50 | NULL     |
|  6 | History |    90 |     140 | NULL     |
|  9 | History |   150 |     290 | 240      |
|  2 | Maths   |    30 |      30 | NULL     |
|  5 | Maths   |    80 |     110 | NULL     |
|  8 | Maths   |   130 |     240 | 210      |
+----+---------+-------+---------+----------+

Upvotes: 2

TriV
TriV

Reputation: 5148

You could use SUM() OVER() like this

 DECLARE @SampleData AS TABLE
 (
    Id int,
    Subject varchar(20),
    Marks int
 )

 INSERT INTO @SampleData
 VALUES 
( 1, 'English', 10),
( 2, 'Maths',   30),
( 3, 'History', 50),
( 4, 'English', 70),
( 5, 'Maths',   80),
( 6, 'History', 90),
( 7, 'English', 100),
( 8, 'Maths',   130),
( 9, 'History', 150 )

SELECT  *, 
       sum(sd.Marks) OVER(PARTITION BY sd.Subject ORDER BY sd.Id) AS [Cumulative sum],
       sum(sd.Marks) OVER(PARTITION BY sd.Subject) - FIRST_VALUE(sd.Marks) OVER(PARTITION BY sd.Subject ORDER BY sd.Id) AS [Cumulative diff(max-min)] 
                                                   -- or try MIN(sd.Marks).....
FROM @SampleData sd
ORDER BY sd.Subject, sd.Id

Demo link: http://rextester.com/GAE89325

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Try this using window functions.

You can use window sum to find cuml sum and then use max and min on that.

select  subject,
    cuml_marks,
    max(cuml_marks) over (partition by subject)
    - min(cuml_marks) over (partition by subject) as cuml_diff
from (
    select subject,
        sum(marks) over (
            partition by subject order by id
            ) as cuml_marks
    from t
    ) t;

Upvotes: 1

Related Questions