CODEWITHSUNDEEP
sql
nimamandarine
nimamandarine

Reputation: 45

Average of top 2

I would like to get the average of the top2 limit1 per policyid. I need my resulting table to also have objectid.

Limit1 and objectid come from the table p_coverage. Policyid comes from the table p_risk. The table p_item is a linking table between p_risk and p_coverage.

The way I thought I should build my query is: create a ranking of limit1 within each policyid. Then take the avg top2.

However the ranking doesn't work and give wrong result. My query works if I take columns from ONE table, but as soon as I add joins between them it gives false ranking.

SELECT policyid, limit1,  /*pcob,*/ RANK() OVER(PARTITION BY policyid ORDER BY limit1 DESC) AS rn
FROM   (SELECT policyid, limit1/*, pc.objectid ASpcob*/ 
        FROM p_risk pr 
        LEFT JOIN p_item 
          ON pr.objectid=p_item.riskobjectid 
        LEFT JOIN p_coverage pc 
          ON p_item.objectid=pc.insuranceitemid) AS s
        ) AS SubQueryAlias
GROUP BY
        policyid, limit1/*, pcob*/, rn
        ORDER BY rn,policyid,limit1 DESC

The table at the end of the picture is what I'd like to have. The first table is the result of the query of Golden Linoff

enter image description here

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

If I understand correctly, you want the ROW_NUMBER() in the subquery and then to aggregate and filter in the outer query:

SELECT policyid, AVG(limit1) as avg_top2_limit1
FROM (SELECT policyid, limit1,
             DENSE_RANK() OVER (PARTITION BY policyid ORDER BY limit1 DESC) as seqnum
      FROM p_risk pr LEFT JOIN
           p_item i
           ON pr.objectid = i.riskobjectid LEFT JOIN
           p_coverage pc 
           ON i.objectid = pc.insuranceitemid) AS s
        ) p
WHERE seqnum <= 2
GROUP BY policyid

Upvotes: 1

nimamandarine
nimamandarine

Reputation: 45

thanks to previous comment! I succeed to do what I wanted. There is the query 

 select b.policyid, avg(b.limit1) as avg_top2_limit1 from(
 SELECT distinct(policyid) policyid, limit1 
 FROM (SELECT policyid, limit1,
         Dense_rank() OVER (PARTITION BY policyid ORDER BY limit1 DESC) as 
seqnum
  FROM p_risk pr LEFT JOIN
       p_item i
       ON pr.objectid = i.riskobjectid LEFT JOIN
       p_coverage pc 
       ON i.objectid = pc.insuranceitemid) AS s

 WHERE seqnum <= 2 ) as b
 GROUP BY policyid`

Upvotes: 1

Related Questions