find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part)

Question

I have a table catalog and I should find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part)

My solution is working, the only thing, that I have a problem with, is the way I want to represent it. I want to have one column in the result that is showing the cost that is averaged over all the suppliers who supply that part. This should be the result:

  sid    pid   cost   avg_cost
 -----  ----- ------ ----------
  s1     p8    11.70   10.71
  s2     p3    0.55    0.52
  s3     p8    12.50   10.71

And this is my result:

  sid    pid   cost   
 -----  ----- ------
  s1     p8    11.70   
  s2     p3    0.55   
  s3     p8    12.50

Here is what i have done:

 SELECT DISTINCT c.sid, c.pid, c.cost   
  FROM catalog AS c
 WHERE c.cost > (SELECT avg(c1.cost) AS avg_cost
            FROM catalog c1
            WHERE c1.pid=c.pid)
GROUP BY c.sid, c.pid, c.cost;

Answer 1

You could turn your subquery into a JOIN, so that its result is available in the outer query :

SELECT c.sid, c.pid, c.cost, c1.avg_cost   
FROM catalog AS c
INNER JOIN (SELECT pid, avg(cost) AS avg_cost FROM catalog GROUP BY pid) c1
    ON c1.pid = c.pid
WHERE c.cost > c1.avg_cost

PS : in your original query, you did not need a GROUP BY since, you did not use aggregate functions in the outer query. Also, based on your data it seems likely that you do not need the DISTINCT functionality.

Answer 2

Window functions (called OLAP functions in DB2) are a very convenient method for doing this:

SELECT c.*  
FROM (SELECt c.*, AVG(c.cost) OVER (PARTITION BY c.pid) as avg_cost
      FROM catalog c
     ) c
WHERE cost > avg_cost;

Window functions are usually faster than JOIN/GROUP BY equivalents.