CODEWITHSUNDEEP
sqlgroup-byoracle-sqldeveloper
user3038436
user3038436

Reputation: 3

Oracle SQL Subqueries

I want to select the person who has worked less than the average total hours that anyone has worked and a camp

Tables are Person and Schedule

this is what I have so far:

 SELECT fname AS "First Name", lname AS "Last Name", 
 SUM((end_time - start_time) * 24)         
 FROM person JOIN schedule USING (person_ID)
 GROUP BY fname
 HAVING SUM((end_time - start_time) * 24) < (
 SELECT AVG(SUM((end_time - start_time) * 24)) FROM schedule);`

Person has fname, lname, and person_ID Schedule has sched_id, s_date, start_time, end_time, and person_id

Thank you!

Getting this error: 

Error starting at line 18 in command:
    SELECT fname AS "First Name", lname AS "Last Name", 
    SUM((end_time - start_time) * 24)     
    FROM person JOIN schedule USING (person_ID)
    GROUP BY fname
    HAVING SUM((end_time - start_time) * 24) < (
    SELECT AVG(SUM((end_time - start_time) * 24)) FROM schedule)
    Error at Command Line:18 Column:31
    Error report:
    SQL Error: ORA-00979: not a GROUP BY expression
    00979. 00000 -  "not a GROUP BY expression"
   *Cause:    
   *Action:

Upvotes: 0

Views: 3323

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think the best way to solve this problem is with analytic functions:

select "First Name", "Last Name", hours
from (SELECT fname AS "First Name", lname AS "Last Name",
             SUM((end_time - start_time) * 24) as hours,
             avg(SUM((end_time - start_time) * 24)) over () as avghours
      FROM person JOIN
           schedule
           USING (person_ID)
      GROUP BY fname, lname
     ) t
WHERE hours < avghours;

The specific problem reported by Oracle is that avg(sum()) is not allowed for a group by (unless, as above, the avg() is really an analytic function).

Upvotes: 0

JanR
JanR

Reputation: 6132

When using an aggregate function such as SUM, you will need to include all your non-aggregate properties in the group by:

 SELECT fname AS "First Name", lname AS "Last Name", 
 SUM((end_time - start_time) * 24)         
 FROM person JOIN schedule USING (person_ID)
 GROUP BY fname, lname --this is changed
 HAVING SUM((end_time - start_time) * 24) < (
 SELECT AVG(SUM((end_time - start_time) * 24)) FROM schedule);`

Upvotes: 0

user2074055
user2074055

Reputation:

The GROUP BY line needs to include all of the non-aggregated fields in your SELECT statement (so everything except for SUM() in this case).

Try changing it to:

GROUP BY fname, lname

Upvotes: 1

Related Questions