0

I need some help on Oracle SQL, appreciate anyone who can advise me on the right query to run.

I have 3 tables as follows:

Patient (**PatientNo**, DocName, ProviderNo, DateRegistered)
PrivateDoctor (**ProviderNo**, **DocName**)
PrivateClinic (**ProviderNo**, Address, TelNo)

Patient: DocName & ProviderNo are foreign keys to PrivateDoctor
PrivateDoctor: ProviderNo is foreign key to PrivateClinic

I need to create a "View" to list all private clinic doctors (including Address, Telephone Number and ProviderNo) who referred Patients to the hospital in last 6 months and the number of patients referred (e.g DateRegistered).

Below is what I tried to run.

SELECT Patient.DocName
      , Patient.ProviderNo
      , PrivateClinic.Address
      , PrivateClinic.TelNo
      , Patient.DateRegistered
      , count (Patient.PatientNo)
FROM Patient 
     JOIN PrivateDoctor ON Patient.ProviderNo = PrivateDoctor.ProviderNo
     JOIN PrivateClinic ON PrivateDoctor.ProviderNo = PrivateClinic.ProviderNo
WHERE Patient.DateRegistered >= ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -5)
AND Patient.DateRegistered < ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)
Group By Patient.DocName;

Below is the error message.

ORA-00979: not a GROUP BY expression
00979. 000 00 - "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 1 Column: 39
user2882008
  • 1
  • 2
  • Searched but do not find any similar questions asked. My oracle SQL view needs to connect all 3 tables together and to count the number of patients registered at the hospital (they were referred by the private clinic doctors to the hospital. – user2882008 Nov 30 '13 at 10:01
  • Sorry the question I meant to link to was [this one](http://stackoverflow.com/q/19217146/146325) (the result order changed when I re-ran the search). Anyway, the error is because the GROUP BY clause must have **all** the non-aggregated columns from the projection. Whether you are joining two or three tables is irrelevant. – APC Nov 30 '13 at 10:12
  • That link is very useful and it has now solved my problem. Thank you. – user2882008 Nov 30 '13 at 11:30

0 Answers0