CODEWITHSUNDEEP
sql-serverrank
user2884535
user2884535

Reputation: 13

SQL Recursive Query - Sum Rows with Lesser Rank

Using MS SQL - 2012 I am trying to run a SQL Query that ranks rows by product id's. Then calculates a sum that represents the rows ranked less than or equal to it. I set up a formula to do this and it works but is slow and resource expensive.

My gut feeling is there is a CTE or Rank function that would do what I want - I just don't know about it.

If a product has 4 rows, using the rank I could then recalculate a sum of lesser rows Qty's.

Product   Order     Qty Rank    Sum  
Apple     John      5   1       5  
Apple     Josh      2   2       7  
Apple     Jacob     1   3       8  
Apple     Jennifer  1   4       9

Row 1 Sum is equal to (Row 1 Qty)
Row 2 Sum is equal to (Row 1 Qty , Row 2 Qty)
Row 3 Sum is equal to (Row 1 Qty , Row 2 Qty , Row 3 Qty )
Row 4 Sum is equal to (Row 1 Qty , Row 2 Qty , Row 3 Qty , Row 4 Qty )

Any help on this would be much appreciated.

Thanks in advance. Josh

Upvotes: 1

Views: 284

Answers (2)

Holmes IV
Holmes IV

Reputation: 1747

If i am understanding this correctly I am thinking you want this. Also this provides sample code, if the first assumptions are wrong please comment.

create table #apples
(product varchar(20)
,orders varchar(20)
,qty int
)

insert into #apples
values ('apple','john',1)
,('apple','john',1)
,('apple','john',1)
,('apple','john',1)
,('apple','john',1)
,('apple','josh',1)
,('apple','josh',1)
,('apple','jacob',1)
,('apple','jennifer',1)
Go

With Tots as 
(Select product,orders,sum(qty) 'Sums', RANK() OVER (partition by product ORDER BY SUM(QTY) desc,orders) 'rnk'
 from #apples
 group by product,orders)

select t1.rnk, SUM(t2.Sums)
from tots t1
    inner join Tots t2
    on t1.rnk >= t2.rnk
    group by t1.rnk

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

For Sql Server 2012+:

select *, sum(qty) over(partition by product order by rank) as [sum]
from products

Sql Server 2008-:

select *, (select sum(qty) from products p2
           where p1.product = p2.product and p2.rank <= p1.rank) as [sum]
from products p1

Upvotes: 1

Related Questions