oracle sql not a group by expression while counting

Question

i'm trying to run the following query that counts the number of doctors with patient referrals within the last 6 months:

SELECT 
    count(local_doc.providerno) NumberofReferals, 
    LOCAL_DOC.FULLNAME FULLNAME, 
    LOCAL_DOC.TELNUMBER TELNUMBER, 
    LOCAL_DOC.STREET STREET, 
    LOCAL_DOC.SUBURB SUBURB, 
    LOCAL_DOC.STATE STATE, 
    LOCAL_DOC.POSTCODE POSTCODE,
    LOCAL_DOC.PROVIDERNO
FROM 
    LOCAL_DOC, 
    PATIENT 
WHERE 
    PATIENT.PROVIDERNO = LOCAL_DOC.PROVIDERNO AND PATIENT.REFERALDATE <= sysdate AND PATIENT.REFERALDATE >= ADD_MONTHS(sysdate, -6)
    group by local_doc.providerno;

i keep getting

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 3 Column: 5

and have no idea how to get around this. How could I do so?

score 3 · Accepted Answer · edited Oct 18 '13 at 08:07

Dear, group By Clause ask a user to specify all Select Columns in Group By Clause as well except select columns with aggregated functions.

Look in my Group by Clause i have specify all columns in Select except local_doc.providerno because it is using Aggregated function COUNT

For better understanding please refer to this link

 SELECT 
    count(local_doc.providerno) NumberofReferals, 
    LOCAL_DOC.FULLNAME FULLNAME, 
    LOCAL_DOC.TELNUMBER TELNUMBER, 
    LOCAL_DOC.STREET STREET, 
    LOCAL_DOC.SUBURB SUBURB, 
    LOCAL_DOC.STATE STATE, 
    LOCAL_DOC.POSTCODE POSTCODE,
    LOCAL_DOC.PROVIDERNO
FROM 
    LOCAL_DOC, 
    PATIENT 
WHERE 
    PATIENT.PROVIDERNO = LOCAL_DOC.PROVIDERNO AND PATIENT.REFERALDATE <= sysdate AND PATIENT.REFERALDATE >= ADD_MONTHS(sysdate, -6)
GROUP BY
    LOCAL_DOC.FULLNAME , 
    LOCAL_DOC.TELNUMBER , 
    LOCAL_DOC.STREET , 
    LOCAL_DOC.SUBURB , 
    LOCAL_DOC.STATE , 
    LOCAL_DOC.POSTCODE ,
    LOCAL_DOC.PROVIDERNO

I tried doing this and oracle gives the following error:`ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 15 Column: 33` — Pindo, Oct 07 '13 at 04:52
@Pindo Sorry alias removed from group by clause , please check now, Thanks — Digital Alchemist, Oct 07 '13 at 04:54

LoztInSpace · Answer 2 · 2013-10-07T06:42:21.403

0

add

GROUP BY 
    LOCAL_DOC.FULLNAME , 
    LOCAL_DOC.TELNUMBER , 
    LOCAL_DOC.STREET , 
    LOCAL_DOC.SUBURB , 
    LOCAL_DOC.STATE , 
    LOCAL_DOC.POSTCODE ,
    LOCAL_DOC.PROVIDERNO

Most DBS other than MYSql will require you to group on the remaining selected columns

edited Oct 07 '13 at 06:42

answered Oct 07 '13 at 04:39

LoztInSpace

5,584
1
15
27

score 0 · Answer 3 · edited Nov 10 '13 at 13:25

in group by you must know which columns can got repeated value in it rows and then add it to your group by the query is:

SELECT 
count(local_doc.providerno) NumberofReferals, 
LOCAL_DOC.FULLNAME FULLNAME, 
LOCAL_DOC.TELNUMBER TELNUMBER, 
LOCAL_DOC.STREET STREET, 
LOCAL_DOC.SUBURB SUBURB, 
LOCAL_DOC.STATE STATE, 
LOCAL_DOC.POSTCODE POSTCODE,
LOCAL_DOC.PROVIDERNO
FROM 
LOCAL_DOC, 
PATIENT 
WHERE 
PATIENT.PROVIDERNO = LOCAL_DOC.PROVIDERNO AND PATIENT.REFERALDATE <= sysdate AND PATIENT.REFERALDATE >= ADD_MONTHS(sysdate, -6)
group by 
LOCAL_DOC.FULLNAME, 
LOCAL_DOC.TELNUMBER, 
LOCAL_DOC.STREET, 
LOCAL_DOC.SUBURB, 
LOCAL_DOC.STATE, 
LOCAL_DOC.POSTCODE,
LOCAL_DOC.PROVIDERNO;

oracle sql not a group by expression while counting

3 Answers3

Linked

Related