SQL GROUP BY - Using COUNT() function

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_NOGROUP 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 pINNER JOIN CONSULTANT c  ON p.P_ID = c.P_IDINNER JOIN DOCTOR d  ON c.S_NO = d.S_NOGROUP 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_IDFROM    PATIENT pINNER JOIN CONSULTANT c1  ON p.P_ID = c1.P_IDINNER 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_NOINNER JOIN DOCTOR d  ON c1.S_NO = d.S_NO

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