CODEWITHSUNDEEP
sqloracle-database
Steve Shields
Steve Shields

Reputation: 37

Oracle SQL find project with most hours logged multiple entries for hours

I am trying to get my query to return the project that has the most hours and the name of that project. I have a query that works for the single largest entry, but I want it to add up the hours if there are more than one entry for that particular project. I know I'm going to have to use a sum and group by pnum, but I can't figure out the specifics it keeps telling me not a group by expression everyway I have tried it.

Here is the code to grab it without adding it up

`SELECT p.pnum, p.pname from workers_hours wh , projects p WHERE wh.hours = (SELECT MAX(hours) as M_Hours FROM workers_hours) AND p.pnum=wh.pnum;`

Here are my tables

CREATE TABLE projects  (pname varchar(255), pnum int PRIMARY KEY, plocation varchar(255))
INSERT INTO projects VALUES(‘Earth Shattering Kaboom’, 101, ‘Mars’)
INSERT INTO projects VALUES('Find Missing Link', 102, 'Bigfoot Woods');
INSERT INTO projects VALUES('Let the Galaxy Burn', 103, 'Abaddons Flagship');
INSERT INTO projects VALUES('Better Lunches', 104, 'Lunch Room');

CREATE TABLE workers_hours (entry int PRIMARY KEY, wnum int, hours int, pnum int)
INSERT INTO workers_hours VALUES (1, 111, 20, 101)
INSERT INTO workers_hours VALUES (1, 166, 80, 103)
INSERT INTO workers_hours VALUES (3, 102, 40, 103)
INSERT INTO workers_hours VALUES (4, 101, 120, 102)
INSERT INTO workers_hours VALUES (5, 102, 40, 104)

Solution:

 SELECT * FROM (SELECT p.pname, sum(wh.hours) as sum_hours
from projects p 
join workers_hours wh on p.pnum = wh.pnum
group by p.pname
ORDER BY sum(wh.hours) desc)  WHERE rownum<=1

Upvotes: 1

Views: 103

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13238

select *
  from projects
 where pnum = (select pnum
                 from (select pnum, sum(hours) as sum_hrs
                         from workers_hours
                        group by pnum
                        order by 2 desc)
                where rownum = 1)

See fiddle at: http://sqlfiddle.com/#!4/5ccbf/4/0

Upvotes: 1

juergen d
juergen d

Reputation: 204884

This returns a list order by the sum of hours for every project

SELECT p.pname, sum(wh.hours) as sum_hours
from projects p 
join workers_hours wh on p.pnum = wh.pnum
group by p.pname  
order by sum(wh.hours) desc

Upvotes: 1

Related Questions