CODEWITHSUNDEEP
sqloracle-database
Ky -
Ky -

Reputation: 32173

How do I both sum 1 table and combine that result with another?

I'm using Oracle SQL and I have two tables:

Employee:
VARCHAR  VARCHAR
EMP_NUM EMP_NAME
------- --------
1       Jane    
1       Jane    
1       Jane    
1       Jane    
2       Alex    
2       Alex    
2       Alex    
3       Danny   
3       Danny   

Assignment:
    NUMBER        FLOAT         FLOAT VARCHAR
ASSIGN_NUM ASSIGN_HOURS ASSIGN_CHARGE EMP_NUM
---------- ------------ ------------- -------
         1          3.5           290 1
         2          2.6          68.2 3
         3            2           406 3
         4          4.7           541 2
         5          3.3            76 1

and I need to combine them into something like this:

TempTable:
VARCHAR  VARCHAR     FLOAT      FLOAT
EMP_NUM EMP_NAME SUM_HOURS SUM_CHARGE
------- -------- --------- ----------
1       Jane          27.2       1464
2       Alex          14.1       1623
3       Danny          9.2      948.4

I've tried using this, but it doesn't work:

SELECT
    Assignment.EMP_NUM,
    Employee.EMP_NAME,
    SUM(Assignment.ASSIGN_HOURS) SUM_HOURS,
    SUM(Assignment.ASSIGN_CHARGE) SUM_CHARGE
  FROM Employee, Assignment
    WHERE Employee.EMP_NUM = Assignment.EMP_NUM
  GROUP BY Assignment.EMP_NUM
;

But SQL complains with:

Error starting at line 1 in command:
SELECT
    Assignment.EMP_NUM,
    Employee.EMP_NAME,
    SUM(Assignment.ASSIGN_HOURS) SUM_HOURS,
    SUM(Assignment.ASSIGN_CHARGE) SUM_CHARGE
  FROM Employee, Assignment
    WHERE Employee.EMP_NUM = Assignment.EMP_NUM
  GROUP BY Assignment.EMP_NUM
Error at Command Line:3 Column:5
Error report:
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:

Even if I remove the GROUP BY line, I get an error at Line:2 Column:5 saying not a single-group group function. Is there any way to get this table

Upvotes: 0

Views: 70

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You need to do the aggregations before you do the joins, otherwise, your numbers will be incorrect:

SELECT e.EMP_NUM, e.EMP_LNAME, a.SUM_HOURS, SUM_CHARGE
FROM (SELECT DISTINCT e.EMP_NUM, e.EMP_LNAME FROM Employee e) e join
     (SELECT a.EMP_NUM,
             SUM(Assignment.ASSIGN_HOURS) as SUM_HOURS,
             SUM(Assignment.ASSIGN_CHARGE) as SUM_CHARGE
      FROM Assignment a
      GROUP BY a.EMP_NUM
     ) a
     on e.EMP_NUM = a.EMP_NUM;

I also changed the join syntax to use explicit joins.

Upvotes: 1

sion_corn
sion_corn

Reputation: 3151

You forgot to include Employee.EMP_LNAME in your GROUP BY clause.

Code should be:

SELECT
    Assignment.EMP_NUM,
    Employee.EMP_LNAME,
    SUM(Assignment.ASSIGN_HOURS) SUM_HOURS,
    SUM(Assignment.ASSIGN_CHARGE) SUM_CHARGE
  FROM Employee, Assignment
    WHERE Employee.EMP_NUM = Assignment.EMP_NUM
  GROUP BY Assignment.EMP_NUM, Employee.EMP_LNAME

Upvotes: 1

MinhD
MinhD

Reputation: 1810

The query should be like this, you need to list all fields in SELECT clause:

SELECT
    Assignment.EMP_NUM,
    Employee.EMP_LNAME,
    SUM(Assignment.ASSIGN_HOURS) SUM_HOURS,
    SUM(Assignment.ASSIGN_CHARGE) SUM_CHARGE
  FROM Employee, Assignment
    WHERE Employee.EMP_NUM = Assignment.EMP_NUM
  GROUP BY Assignment.EMP_NUM, 
           Employee.EMP_LNAME
;

Upvotes: 1

Related Questions