SQL GROUP BY - Using COUNT() function

Question

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.

Answer 1

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.