CODEWITHSUNDEEP
sqldatabasederby
Ben
Ben

Reputation: 11

SQL Query in Apache Derby

I'm working on a side project with a Apache Derby database and I'm having trouble with a query. I have the following two tables:

    create table Employee(
    ID VARCHAR(7) not null, 
    lname VARCHAR(30) NOT NULL,
     fname VARCHAR(30) NOT NULL,
     avgPieces int, 
    PRIMARY KEY(ID));

   create table Record(
   emp_id VARCHAR(7) references Employee(ID), 
   day DATE NOT NULL,
   pieces int NOT NULL, 
   numMisloads int);

I'm trying to get a query to return sum(pieces)/sum(numMisloads) for a given employee. I have the following query but I get an error saying that since there is an aggregate all the returns need to have a valid aggregate.

    SELECT lname, fname, (SUM(Record.pieces)/SUM(Record.numMisloads)) 
FROM Employee, Record Where Employee.id = Record.emp_id GROUP BY Employee.id;

Pretty stuck, any help would be appreciated.

Upvotes: 1

Views: 1813

Answers (1)

Jacob
Jacob

Reputation: 43219

What I do in those situations is using FIRST() on lname, because lname is always the same. It feels like a dirty work-around so I hope somebody comes up with a better solution.

SELECT FIRST(lname), FIRST(fname), (SUM(Record.pieces)/SUM(Record.numMisloads)) 
FROM Employee, Record Where Employee.id = Record.emp_id GROUP BY Employee.id;

Upvotes: 1

Related Questions