SQL GROUP BY - Using COUNT() function

group-by, ora-00979, oracle, sql

Solution

Since you are using an aggregate function, your fields in the `SELECT` list that are not being aggregated need to be in the `GROUP BY`:

SELECT  CONSULTANT.S_NO, DOCTOR.D_NAME, CONSULTANT.SPEC, 
PATIENT.P_ID, PATIENT.P_NAME, COUNT(CONSULTANT.P_ID)
FROM    PATIENT, CONSULTANT, DOCTOR                            
WHERE   PATIENT.P_ID = CONSULTANT.P_ID
   AND     CONSULTANT.S_NO = DOCTOR.S_NO
GROUP BY CONSULTANT.S_NO, DOCTOR.D_NAME, CONSULTANT.SPEC, PATIENT.P_ID, PATIENT.P_NAME

As a side note, I would also use ANSI JOIN syntax instead of the comma separated list of tables:

SELECT  c.S_NO, d.D_NAME, c.SPEC, p.P_ID, p.P_NAME, COUNT(c.P_ID)
FROM    PATIENT p
INNER JOIN CONSULTANT c
  ON p.P_ID = c.P_ID
INNER JOIN DOCTOR d
  ON c.S_NO = d.S_NO
GROUP BY c.S_NO, d.D_NAME, c.SPEC, p.P_ID, p.P_NAME

Now, since you need to add the additional fields to the `GROUP BY` this could adjust the `COUNT()` total to numbers that you are not expecting. So you might need to incorporate a sub-query to get the total count, similar to this:

SELECT  c1.S_NO, d.D_NAME, c1.SPEC, p.P_ID, p.P_NAME, c2.Count_P_ID
FROM    PATIENT p
INNER JOIN CONSULTANT c1
  ON p.P_ID = c1.P_ID
INNER JOIN
(
  select COUNT(c.P_ID) Count_P_ID, S_NO
  from CONSULTANT c
  group by S_NO
) c2
  ON c1.S_NO = c2.S_NO
INNER JOIN DOCTOR d
  ON c1.S_NO = d.S_NO

This allows you to then `GROUP BY` the one field that you initially wanted.

Problem

I've been doing a task which involves creating a database for a hospital and I've been encountering a very frustrating error which I cannot seem to fix no matter how much research that I do. The error I received is: ERROR at line 1: ORA-00979: not a GROUP BY expression The structure of my code for inserting the values is: ``` SELECT CONSULTANT.S_NO, DOCTOR.D_NAME, CONSULTANT.SPEC, PATIENT.P_ID, PATIENT.P_NAME, COUNT(CONSULTANT.P_ID) FROM PATIENT, CONSULTANT, DOCTOR WHERE PATIENT.P_ID = CONSULTANT.P_ID AND CONSULTANT.S_NO = DOCTOR.S_NO GROUP BY CONSULTANT.S_NO; ``` And the structure of my tables are: ``` CREATE TABLE PATIENT ( P_ID NUMBER NOT NULL, P_NAME CHAR(20), ADDRESS VARCHAR(20), DOB DATE, WARD_NO NUMBER NOT NULL, C_S_NO NUMBER NOT NULL, CONSTRAINT PK_PATIENT PRIMARY KEY(P_ID) ); CREATE TABLE DOCTOR ( S_NO NUMBER NOT NULL, D_NAME CHAR(20), APP_DATE DATE, CONSTRAINT PK_DOC PRIMARY KEY(S_NO) ); CREATE TABLE CONSULTANT ( S_NO NUMBER NOT NULL, P_ID NUMBER NOT NULL, SPEC CHAR(20), T_CODE VARCHAR(20) NOT NULL, CONSTRAINT PK_CDOC PRIMARY KEY(S_NO) ); ``` Would really appreciate any help anyone could give me on solving this dilemma.

Original source