0

I was trying to calculate the cumulative sum of a column in SQL Server 2008 R2 after sorting the column in ascending order. I cannot use rows unbounded preceding or following as it is only available for SQL Server 2012 and beyond. I used the following code :

select
    sub_code,
    Roll_no,
    Total_marks,
    sum (Total_marks) over (order by Total_marks ASC) as cumulative_Total  
from table

But I get an error:

Incorrect syntax near 'order'

How to resolve this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anita Mathew
  • 183
  • 1
  • 15

3 Answers3

4

One option which doesn't use a window function would be to use a correlated subquery to calculate the running total:

select
    t1.sub_code,
    t1.Roll_no,
    t1.Total_marks,
    (select sum(Total_marks) from table t2
     where t2.Total_marks <= t1.Total_marks) as cumulative_Total
from table t1
order by t1.Total_marks

This assumes that you really want to order the running total using the thing you are trying to sum, namely Total_marks. In general, you could use the following correlated subquery if you intend to use a different column for ordering:

(select sum(Total_marks) from table t2
 where t2.some_col <= t1.some_col) as cumulative_Total
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • This got a bit confusing .. I just want to reorder the total_marks column in ascending order and then generate a new column for cumulative marks..have never used correlated subqueries. – Anita Mathew May 29 '17 at 07:37
  • There is no "reordering" of a table inside a database; the only order is the one you specify when you query. Did you try my query and, if so, what problems do you have? – Tim Biegeleisen May 29 '17 at 07:38
  • offcourse reordering would be done on the result set ..your query is using a second table .Do i need to first transfer these rows to a temp table? – Anita Mathew May 29 '17 at 07:42
  • t1 and t2 are aliases for the same table. You need the correlated subquery to get running total because SQL Server 2008 R2 doesn't support use of order by clause in analytic/window sum function. You need to upgrade to SS 2012 for that. – Lord Peter May 29 '17 at 08:30
  • So do i have to use t1, t2 as in the code above or the actual table name? Anyways it gives the multipart identifier could not be bound error – Anita Mathew May 29 '17 at 09:03
  • @AnitaMathew Do you know what an _alias_ is in SQL? `t1` and `t2` are placeholders for your actual two table names. – Tim Biegeleisen May 29 '17 at 09:53
  • I understand that now..but it still gives the multipart identifier could not be bound error – Anita Mathew May 30 '17 at 07:08
1

Check out Aaron Bertrand's excellent answer with multiple options including pros and cons.

https://stackoverflow.com/a/11313533/3266499

Mazhar
  • 3,797
  • 1
  • 12
  • 29
0

I managed to solve this issue by using a stored procedure which arranges the scores range in ascending order and displays number of student per range value after which it calculates the cumulative score .

 USE [databasename]
GO
/****** Object:  StoredProcedure [dbo].[sp_range]    Script Date: 8/26/2019 12:30:10 
PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_range]
@sub_code nvarchar(10)

AS

SET NOCOUNT ON;
;WITH data AS 
( 
SELECT CASE  
   WHEN Total_marks BETWEEN 10 and 20 THEN '10-20'
   WHEN Total_marks BETWEEN 21 and 30 THEN '21-30'
   WHEN Total_marks BETWEEN 31 and 40 THEN '31-40'
   WHEN Total_marks BETWEEN 41 and 50 THEN '41-50'
   WHEN Total_marks BETWEEN 51 and 60 THEN '51-60'
   WHEN Total_marks BETWEEN 61 and 70 THEN '61-70'
   WHEN Total_marks BETWEEN 71 and 80 THEN '71-80'
   WHEN Total_marks BETWEEN 81 and 90 THEN '81-90'
   WHEN Total_marks BETWEEN 91 and 100 THEN '91-100'
   end as Score_Acheived,
   No_of_Students=COUNT(1)
   from tablename 
   where sub_code=@sub_code
    GROUP BY 
        (
        CASE  
       WHEN Total_marks BETWEEN 10 and 20 THEN '10-20'
       WHEN Total_marks BETWEEN 21 and 30 THEN '21-30'
       WHEN Total_marks BETWEEN 31 and 40 THEN '31-40'
       WHEN Total_marks BETWEEN 41 and 50 THEN '41-50'
       WHEN Total_marks BETWEEN 51 and 60 THEN '51-60'
       WHEN Total_marks BETWEEN 61 and 70 THEN '61-70'
       WHEN Total_marks BETWEEN 71 and 80 THEN '71-80'
       WHEN Total_marks BETWEEN 81 and 90 THEN '81-90'
       WHEN Total_marks BETWEEN 91 and 100 THEN '91-100' end
       )
)
SELECT 
    d.Score_Acheived,
    d.No_of_Students, 
    Accumulate_Total_Score = SUM(d2.No_of_Students)
FROM 
    data d
INNER JOIN 
    data AS d2 ON d.Score_Acheived >= d2.Score_Acheived
GROUP BY 
    d.Score_Acheived, 
    d.No_of_Students
ORDER BY 
    d.Score_Acheived;
Anita Mathew
  • 183
  • 1
  • 15