SQL GROUP BY - 使用 COUNT() 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13683663/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL GROUP BY - Using COUNT() function
提问by Barry McAuley
I've been doing a task which involves creating a database for a hospital and I've been encountering a very frustrating error which I cannot seem to fix no matter how much research that I do.
我一直在做一项涉及为医院创建数据库的任务，但我遇到了一个非常令人沮丧的错误，无论我做了多少研究，我似乎都无法解决这个错误。
The error I received is:
我收到的错误是：
ERROR at line 1: ORA-00979: not a GROUP BY expression
第 1 行错误：ORA-00979：不是 GROUP BY 表达式
The structure of my code for inserting the values is:
我插入值的代码结构是：
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_NO
GROUP BY CONSULTANT.S_NO;
And the structure of my tables are:
我的表的结构是：
CREATE TABLE PATIENT (
P_ID NUMBER NOT NULL,
P_NAME CHAR(20),
ADDRESS VARCHAR(20),
DOB DATE,
WARD_NO NUMBER NOT NULL,
C_S_NO NUMBER NOT NULL,
CONSTRAINT PK_PATIENT PRIMARY KEY(P_ID)
);
CREATE TABLE DOCTOR (
S_NO NUMBER NOT NULL,
D_NAME CHAR(20),
APP_DATE DATE,
CONSTRAINT PK_DOC PRIMARY KEY(S_NO)
);
CREATE TABLE CONSULTANT (
S_NO NUMBER NOT NULL,
P_ID NUMBER NOT NULL,
SPEC CHAR(20),
T_CODE VARCHAR(20) NOT NULL,
CONSTRAINT PK_CDOC PRIMARY KEY(S_NO)
);
Would really appreciate any help anyone could give me on solving this dilemma.
非常感谢任何人可以为我解决这个难题提供的任何帮助。
回答by Taryn
Since you are using an aggregate function, your fields in the SELECTlist that are not being aggregated need to be in the GROUP BY:
由于您使用的是聚合函数，因此SELECT列表中未聚合的字段需要位于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_NO
GROUP 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:
作为旁注，我还将使用 ANSI JOIN 语法而不是逗号分隔的表列表：
SELECT c.S_NO, d.D_NAME, c.SPEC, p.P_ID, p.P_NAME, COUNT(c.P_ID)
FROM PATIENT p
INNER JOIN CONSULTANT c
ON p.P_ID = c.P_ID
INNER JOIN DOCTOR d
ON c.S_NO = d.S_NO
GROUP 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 BYthis 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:
现在，由于您需要将附加字段添加到GROUP BY这可能会将COUNT()总数调整为您不期望的数字。因此，您可能需要合并一个子查询来获取总计数，类似于：
SELECT c1.S_NO, d.D_NAME, c1.SPEC, p.P_ID, p.P_NAME, c2.Count_P_ID
FROM PATIENT p
INNER JOIN CONSULTANT c1
ON p.P_ID = c1.P_ID
INNER 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_NO
INNER JOIN DOCTOR d
ON c1.S_NO = d.S_NO
This allows you to then GROUP BYthe one field that you initially wanted.
这使您可以GROUP BY使用您最初想要的一个字段。
