SQL Cumulative sum

Reply
Contributor
Posts: 62

SQL Cumulative sum

I want the cumulative sum of rating 

 

no rating

1  2

2  4

3  5

4  9

5  3

6  3

7  8

8  3

9  7

so if i use this query 

proc sql;
select *,(select sum(rating)as cum from t b where b.no le a.no ) from t a;

I get the desired result .e cumulative sum from top to bottom and if i use

proc sql;
select *,(select sum(rating)as cum from t b where b.no ge a.no ) from t a;

the cmulative sum is showing from bottom to top 

please explain the logic

Contributor
Posts: 20

Re: SQL Cumulative sum

Hi,

 

In the subquery, for the first record - filter with b.no<=a.no returns the sum of first one record whereas the filter with b.no >= a.no returns the sum of all records.

 

Likewise, for the second record with 'le' filter returns the sum of first two records whereas with 'ge' filter returns the sum of remaining records except the first one.

 

Hence you see bottom up cumulative sum for 'ge' condition.

 

Thanks,

Manohar

Contributor
Posts: 62

Re: SQL Cumulative sum

a b

1 1

2 2

3 3

4 4

5 5

so if we say b.no<=a.no then it will be like 1<=1 and then it will check 2<=1 and then 2<=2.That's fine.

 

But if we say b.no>=a.no then how it is processing?

1>=1 and then 1>=2

can u please explain with taking these example.

Ask a Question
Discussion stats
  • 2 replies
  • 379 views
  • 0 likes
  • 2 in conversation