CODEWITHSUNDEEP
sqloracle-databaseselect
SteppingHat
SteppingHat

Reputation: 1362

SQL SELECT query to pull data from tables to calculate a sum

I have four tables in a database, what I want to do is essentially list the employee number, name department number of any projects they are working on and the total hours that they spend working on projects.

I'm trying to do this all as one single SELECT statement, but am having trouble getting it to work with Oracle SQL. My latest attempt has been

SELECT EMPLOYEE.E#, EMPLOYEE.NAME, SUM(WORKSON.HOURS)
FROM EMPLOYEE JOIN WORKSON ON EMPLOYEE.E#=WORKSON.E#
GROUP BY EMPLOYEE.E#;

Which isn't even the complete statement, and gives me the error:

ERROR at line 1:
ORA-00979: not a GROUP BY expression

What can I do to complete the statement to also include the department number and what should I be doing? Should I be using a join for this? or should I be incorporating another SELECT statement within a WHERE clause?

The tables are as follows:

EMPLOYEE:

SQL> SELECT * FROM EMPLOYEE;            

E#    NAME               DOB         S  SALARY SUPER    D#
----- ------------------ ----------- -- ------ -------- ---
00100 Albert             13-OCT-65   M   186.5
00110 Alvin              13-OCT-77   M   156.4 00100      1
00120 Alice              17-JUN-73   F   156.5 00100      2
00150 Bob                02-JUL-60   M   166.4 00100      3
00200 Carl               02-FEB-67   M   156.3 00100      4
00250 Douglass           14-APR-83   M   156.4 00100      5
00101 Peter              13-NOV-76   M    85.2 00110      1
00103 Ami                12-SEP-85   F    78.2 00110      1
00107 Wendy              12-SEP-88   F    68.2 00110      1
00109 Michael            12-SEP-90   M    58.2 00110      1
00125 Angela             20-NOV-90   F    56.4 00120      2
00105 Robert             15-JAN-86   M    66.2 00150      3
00136 Aban               15-JAN-90   M    55.3 00200      4
00187 Eadger             07-APR-86   M    76.5 00250      5

14 rows selected.

WORKSON:

SQL> SELECT * FROM WORKSON;

E#    P#      HOURS
----- ------- -----
00110    1001    10
00101    1001    20
00150    1002    10
00105    1002    10
00105    1003    20
00105    1004    20
00250    1004    15
00187    1004    25
00105    1005    15

DEPARTMENT:

SQL> SELECT * FROM DEPARTMENT;

D# DNAME              MANAG  MSDATE
-- ------------------ ------ ------------
 1 SALES              00110  02-JAN-12
 2 ACCOUNTING         00120  30-OCT-10
 3 GAMES              00150  01-MAR-08
 4 HUMAN RESOURCES    00200  02-JAN-13
 5 SPORTS             00250  10-MAY-10

PROJECT:

SQL> SELECT * FROM PROJECT;

    P# PTITLE             SPONSOR                D#  BUDGET
------ --- -------------- ---------------------- --- -------
  1001 Computation        Microsoft               1    25000
  1002 Study methods      Education committee     3    15000
  1003 Racing car         Cloud Pty Ltd           3   225000
  1004 Football           Football club           5    35000
  1005 Swimming           Education committee     5   125000

Any help would be greatly appreciated. Thanks in advance!

Upvotes: 0

Views: 66

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

You first find the sum of hours of each employee and then join the same with your main table. Inline view can be used for the same.

SELECT EMPLOYEE.E#, EMPLOYEE.NAME, NVL(WORKSON_AGGR.TOTAL_HOURS,0)
FROM EMPLOYEE
  LEFT JOIN  (SELECT E#, SUM(HOURS) as TOTAL_HOURS
                FROM WORKSON
          GROUP BY E# ) WORKSON_AGGR
     ON (WORKSON_AGGR.E# = EMPLOYEE.E#)

Now, you can join your department table too easily.

SELECT 
     E.E#,
     E.NAME,
     NVL(WORKSON_AGGR.TOTAL_HOURS,0),
     D.DNAME
FROM EMPLOYEE E
  LEFT JOIN  (SELECT E#, SUM(HOURS) as TOTAL_HOURS
                FROM WORKSON
             GROUP BY E# ) WORKSON_AGGR
     ON (WORKSON_AGGR.E# = E.E#)
  JOIN DEPARTMENT D ON (D.D# = E.D#)

Upvotes: 2

Related Questions