NOT A GROUP BY EXPRESSION ERROR (Oracle)

Question

Below is my sql query in oracle 12c. I'm using sqlplus. It's prompting the error "ORA-00979: not a GROUP BY expression". I don't know what when wrong here. Please help.

PROMPT Type the speciality
ACCEPT speciality PROMPT "Speciality: "
SELECT assignment.doctor_id, doctors.name, MAX(assignment.end_assignment) AS end_contract,
(SELECT doctor_availability.end_availability
 FROM doctor_availability
 JOIN doctor_specialities ON doctor_specialities.doctor_id = doctors.id
 AND UPPER(doctor_specialities.specialities) like UPPER('%&speciality%'))
FROM assignment
JOIN doctors ON doctors.id = assignment.doctor_id
JOIN doctor_availability ON doctor_availability.doctor_id = assignment.doctor_id
WHERE doctor_availability.end_availability > 'end_contract'
GROUP BY assignment.doctor_id;

enter image description here

You need to `GROUP BY` `doctors.name` as well as `assignment.doctor_id` — paul, Apr 17 '14 at 08:20
Could it be that here: SELECT doctor_availability.end_availability FROM doctor_availability JOIN doctor_specialities ON doctor_specialities.doctor_id = doctors.id you have to use doctor_availability.doctors.id becaus you join the table doctor_availability with the table doctor_specialities — OliC, Apr 17 '14 at 09:07

score 3 · Answer 1 · answered Apr 17 '14 at 08:25

Normally if you use the Group by clause you have to put there all the fields which you select without a aggregat function. In your case you select assignment.doctor_id, doctors.name without a aggregate function but in the group by clause you have only the assignment.doctor_id. Try this

SELECT assignment.doctor_id, doctors.name, MAX(assignment.end_assignment) AS end_contract,
(SELECT doctor_availability.end_availability
 FROM doctor_availability
 JOIN doctor_specialities ON doctor_specialities.doctor_id = doctors.id
 AND UPPER(doctor_specialities.specialities) like UPPER('%&speciality%'))
FROM assignment
JOIN doctors ON doctors.id = assignment.doctor_id
JOIN doctor_availability ON doctor_availability.doctor_id = assignment.doctor_id
WHERE doctor_availability.end_availability > 'end_contract'
GROUP BY assignment.doctor_id, doctors.name

Matt · Answer 2 · 2014-04-17T08:39:18.860

2

You need to contain all your select values in the group by.

PROMPT Type the speciality
ACCEPT speciality PROMPT "Speciality: "
SELECT assignment.doctor_id, doctors.name, MAX(assignment.end_assignment) AS end_contract,
(SELECT doctor_availability.end_availability
FROM doctor_availability
JOIN doctor_specialities ON doctor_specialities.doctor_id = doctors.id
AND UPPER(doctor_specialities.specialities) like UPPER('%&speciality%'))
FROM assignment
JOIN doctors ON doctors.id = assignment.doctor_id
JOIN doctor_availability ON doctor_availability.doctor_id = assignment.doctor_id
WHERE doctor_availability.end_availability > 'end_contract'
GROUP BY assignment.doctor_id, doctors.name, doctor_availability.end_availability;

edited Apr 17 '14 at 08:39

answered Apr 17 '14 at 08:20

Matt

14,906
27
99
149

I had tried by adding the doctors.name earlier but it still the same error. I updated the with the error screenshot. – Roshan Apr 17 '14 at 08:28
Thats because there is a subselect in the select statement as well, and this is not in the group by statement. – nightfox79 Apr 17 '14 at 08:33
@Matt, it is still the same error. – Roshan Apr 17 '14 at 08:50
@t3es screenshot the error again please with the additional group by in there – Matt Apr 17 '14 at 09:02

NOT A GROUP BY EXPRESSION ERROR (Oracle)

2 Answers2