oracle sql在计数时不是按表达式分组 [英] oracle sql not a group by expression while counting

问题描述

我正在尝试运行以下查询来计算过去 6 个月内转诊患者的医生数量:

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;

我一直在接受

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?

推荐答案

亲爱的，按子句分组要求用户指定所有选择列除了选择带有聚合的列之外，也可以按子句分组功能.

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

按条款查看我的组，我在 Select 中指定了所有列，除了local_doc.providerno 因为它使用了聚合函数 COUNT

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

这篇关于oracle sql在计数时不是按表达式分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！