Oracle SQL query question

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.

A subquery is a query within a query. I won't give you the answer but here is a link that describe subqueries: [Oracle Subquery on Tech on the net][1]. I think you will get more out of the assignment learning how to do it on your own rather than fishing for what someone else's answers. [1]: techonthenet.com/oracle/subqueries.php — scrappedcola
– scrappedcola, Commented Apr 12, 2011 at 16:00

John Hartsock · Accepted Answer · 2011-04-12 17:39:40Z

3

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

edited Apr 12, 2011 at 17:39

answered Apr 12, 2011 at 15:58

John Hartsock

87.1k23 gold badges135 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

LunarZer0 Over a year ago

That one is the closest so far, except the output only shows the lowest MIN(AVG()) and doesn't show DeptNo.

John Hartsock Over a year ago

@LunarZer0 OK so if you take the Avg(of project cost) grouping by DeptNo then you will get 1 row for each department. If you get the min of the Averages just queried, grouping by department you will get the same result. look at my edit in my answer

LunarZer0 Over a year ago

Your solution does work in giving me the lowest average by dept, however the problem is simple that they are wanting the output to say "DeptNo, Min(AVG(projcost))" and it is only giving me "MIN(AVG(projcost))" I'm trying to work in the DeptNo output.

John Hartsock Over a year ago

@LunarZer0 Your last comment is seems incomplete

LunarZer0 Over a year ago

@John Yea I accidentally hit the enter button while typing. I did edit it to finish the sentence. Let me know if you cannot see the edit.

|

Chandu · Accepted Answer · 2011-04-12 16:07:45Z

1

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

answered Apr 12, 2011 at 16:07

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

Comments

sensware · Accepted Answer · 2011-04-12 17:25:35Z

-1

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

answered Apr 12, 2011 at 17:25

sensware

211 bronze badge

Comments

user3796386 · Accepted Answer · 2017-04-04 10:44:36Z

-1

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

edited Apr 4, 2017 at 10:44

answered Apr 4, 2017 at 9:40

user3796386

212 bronze badges

Collectives™ on Stack Overflow

Oracle SQL query question

4 Answers 4

11 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

11 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related