CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Red Devil
Red Devil

Reputation: 2393

Calculation of points if there are 2 student with same rank in sql server

Rank_Table

ID  Rank    
1   1   
2   1   
3   3   
4   3   
5   5

Price

No  Points  
1   10  
2   9   
3   8   
4   7   
5   6

Expected Output

ID  Rank    Points  
1   1       9.5     
2   1       9.5     
3   3       7.5     
4   3       7.5     
5   5       6

2nd rank is not present so 1st and 2nd points are sum together and distributed among the number of the student for eg : (10+9) / 2 = 9.5

When I join the 2 table like

select *
from Rank_table a join
     Price b
     on a.ID = b.No

I am getting the output as

ID  Rank    No  Points  
1   1      1    10  
2   1      2    9   
3   3      3    8   
4   3      4    7   
5   5      5    6

Upvotes: 2

Views: 74

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269733

Hmmm. You seem to want a non-equijoin based on the "next" rank as well as the rank in each row. Unfortunately, SQL Server 2008 doesn't support lead(), but you can use apply:

select rt.id, rt.rank, avg(p.price * 1.0) as points
from rank_table rt outer apply
     (select min(rt2.rank) as next_rank
      from rank_table rt2
      where rt2.rank > rt.rank
     ) rt2 left join
     price p
     on p.no >= rt.rank >= p.no and
        (p.no < rt2.next_rank or rt2.next_rank is null)
group by rt.id, rt.rank;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need simple JOIN :

select rn.*,
       avg(convert(decimal(10,0), p.Points)) over (partition by rn.rnk) as points
from Rank_Table rn inner join 
     Price p
     on p.id = rn.No;

Upvotes: 2

Thom A
Thom A

Reputation: 95571

This seems to be quite a simple requirement, simply using AVG and an OVER clause.

CREATE TABLE [Rank] (ID int, [Rank] int)
CREATE TABLE Price ([No] int, Points int);
GO

INSERT INTO [Rank]
VALUES
(1,1),   
(2,1),   
(3,3),   
(4,3),   
(5,5);   

INSERT INTO Price
VALUES
(1,10),  
(2,9),  
(3,8),  
(4,7),  
(5,6);
GO
SELECT R.ID,
       R.[Rank],
       AVG(CONVERT(decimal(2,0),P.Points)) OVER (PARTITION BY R.[Rank]) AS Points
FROM [Rank] R
     JOIN Price P ON R.ID = P.[No];

GO
DROP TABLE [Rank];
DROP TABLE Price;

Upvotes: 3

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can calculate AVG at rank level and then join back to Rank_Table like in this working demo

select R.*,T.points from Rank_table R
JOIN
(
select rank, points=avg(cast(points as decimal(10,2)))
from Rank_table a join
     Price b
     on a.ID = b.No
 group by rank
    )T
    on T.rank=R.rank

Upvotes: 0

kiran gadhe
kiran gadhe

Reputation: 743

SELECT *, 
   AA.pts AS POINTS 
FROM   rank_table R 
   INNER JOIN (SELECT rank, 
                      Sum(points) / Count(*) AS PTS 
               FROM   rank_table a 
                      JOIN price b 
                        ON a.id = b.no 
               GROUP  BY rank)AA 
           ON ( R.rank = AA.rank )

Upvotes: 0

Related Questions