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屋!