CODEWITHSUNDEEP
sqloracle-database
LunarZer0
LunarZer0

Reputation: 3

Oracle SQL query question

I'm trying to complete this Oracle SQL query with no luck.

"List the department that has the least average cost. (Hint: SELECT MIN(AVG(cost)) is valid as long as no other column is combined in the SELECT statement. You can combine the above as a subquery with another query)"

I'm able to get it to display the Depts with their average cost, but when I try to work in MIN it breaks. This is what I have so far.

SELECT DeptNo, AVG(projcost) FROM projects, clinicians WHERE clinicians.Clinicianno = projects.Clinicianno GROUP BY DeptNo;

That will give me the depts and their AVG, but when I change AVG(projcost) to be MIN(AVG(projcost) it breaks. The hint is to make it a subquery but I can't figure out how to do that yet. Any help would be much appreciated.

Upvotes: 0

Views: 972

Answers (4)

user3796386
user3796386

Reputation: 21

Try this....

Sample Data

DEPTNO| AVG_COST 100 |8601.333333 30 |4150 90 |19333.33333 20 |9500 70 |10000 110 |10154 50 |3475.555556 80 |8955.882353 40 |6500 60 |5760 10 |4400

SELECT DEPTNO,AVG_COST FROM (SELECT P.DEPTNO, (AVG(C.COST)) AVG_COST FROM PROJECTS P, CLINICIANS C WHERE P.DEPTNO=C.DEPTNO GROUP BY P.DEPTNO ) WHERE AVG_COST= (SELECT MIN(AVG_COST) FROM (SELECT P.DEPTNO, (AVG(C.COST)) AVG_COST FROM PROJECTS P, CLINICIANS C WHERE P.DEPTNO=C.DEPTNO GROUP BY P.DEPTNO ) );

Above query will give you below result

DEPTNO| AVG_COST 50 |3475.555556

Upvotes: -1

John Hartsock
John Hartsock

Reputation: 86892

SELECT 
  MIN(q1.AvgProjCost)
FROM (SELECT 
        DeptNo, 
        AVG(projcost) as AvgProjCost 
      FROM projects, clinicians 
      WHERE clinicians.Clinicianno = projects.Clinicianno 
      GROUP BY DeptNo) q1

EDITED Suppose I Have the following department project costs

Department ProjectCost
1          15
1          15
1          15
2          16
2          16
3          17
3          17
4          18

These project Costs would render the following averages

Department Average
1          15
2          16
3          17
4          18

The minimum average for each department is still the same result set.

EDITED AGAIN

If you really must have Minimum Average for each department then this will work

SELECT 
  q1.Dept,
  MIN(q1.AvgProjCost)
FROM (SELECT 
        DeptNo, 
        AVG(projcost) as AvgProjCost 
      FROM projects, clinicians 
      WHERE clinicians.Clinicianno = projects.Clinicianno 
      GROUP BY DeptNo) q1
GROUP BY q1.Dept

However you will soon realize that this result set is and will always be the same as

      SELECT 
        DeptNo, 
        AVG(projcost) as AvgProjCost 
      FROM projects, clinicians 
      WHERE clinicians.Clinicianno = projects.Clinicianno 
      GROUP BY DeptNo

EDITED Once More

To obtain the department with the smallest average project cost 

SELECT 
  q1.Dept,
  q1.AvgProjCost
FROM (SELECT 
        DeptNo, 
        AVG(projcost) as AvgProjCost 
      FROM projects, clinicians 
      WHERE clinicians.Clinicianno = projects.Clinicianno 
      GROUP BY DeptNo) q1
WHERE rownum = 1
ORDER BY AvgProjCost DESC

Upvotes: 3

sensware
sensware

Reputation: 21

SELECT TOP 1 DeptNo ,AVG(projcost) FROM projects ,clinicians WHERE clinicians.Clinicianno = projects.Clinicianno GROUP BY DeptNo ORDER BY AVG(projcost) ;

Upvotes: -1

Chandu
Chandu

Reputation: 82943

Try this:

WITH avgData AS
SELECT a.*,
             RANK() OVER(ORDER BY avg_cost) RN
  FROM 
    (
        SELECT  DeptNo, 
                    AVG(projcost) avg_cost,
                    projects.ROWNUM rn
        FROM    projects, 
                    clinicians 
        WHERE   clinicians.Clinicianno = projects.Clinicianno 
        GROUP BY DeptNo
    ) a
SELECT *
  FROM avgData
 WHERE RN = 1

Upvotes: 1

Related Questions