SQL 不是 GROUP BY 表达式错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13962772/
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
Not a GROUP BY expression error
提问by memyselfandmyiphone
I'm relatively new to databases. I am using Oracle and I'm trying to implement this query to find the number of personal training sessions the member has had.
我对数据库比较陌生。我正在使用 Oracle 并且我正在尝试实施此查询以查找成员已参加的个人培训课程的数量。
The tables are;
表是;
MEMBERS
会员
MEMBERS_ID(NUMBER),
MEMBERSHIP_TYPE_CODE(VARCHAR),
ADDRESS_ID(NUMBER), CLUB_ID(NUMBER)
MEMBER_NAME(VARCHAR),
MEMBER_PHONE(VARCHAR),
MEMBER_EMAIL(VARCHAR)
PERSONAL_TRAINING_SESSIONS
PERSONAL_TRAINING_SESSIONS
SESSION_ID(VARHCAR),
MEMBER_ID (NUMBER),
STAFF_ID(VARCHAR),
SESSION_DATETIME(DATE)
My query is returing this error:
我的查询正在返回此错误：
ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause:
*Action: Error at Line: 1 Column: 8
ORA-00979: 不是 GROUP BY 表达式 00979。00000 - “不是 GROUP BY 表达式” *原因：
*操作：行错误：1 列：8
SELECT MEMBERS.MEMBER_ID,MEMBERS.MEMBER_NAME, COUNT(personal_training_sessions.session_id)
FROM MEMBERS JOIN personal_training_sessions
ON personal_training_sessions.member_id=members.member_id
GROUP BY personal_training_sessions.session_id;
Can anyone point me in the right direction? I have looked around do I need to separate the count query?
任何人都可以指出我正确的方向吗？我环顾四周是否需要分离计数查询？
回答by Ben
The error says it all, you're not grouping by MEMBERS.MEMBER_IDand MEMBERS.MEMBER_NAME.
错误说明了一切，您没有按MEMBERS.MEMBER_ID和分组MEMBERS.MEMBER_NAME。
SELECT MEMBERS.MEMBER_ID, MEMBERS.MEMBER_NAME
, COUNT(personal_training_sessions.session_id)
FROM MEMBERS
JOIN personal_training_sessions
ON personal_training_sessions.member_id = members.member_id
GROUP BY MEMBERS.MEMBER_ID, MEMBERS.MEMBER_NAME
You want the count of personal sessions permember, so you need to group by the member information.
您需要每个成员的个人会话数，因此您需要按成员信息进行分组。
The basic (of course it can get a lot more complex) GROUP BY, SELECT query is:
基本的（当然它可以变得更复杂） GROUP BY, SELECT 查询是：
SELECT <column 1>, <column n>
, <aggregate function 1>, <aggregate function n>
FROM <table_name>
GROUP BY <column 1>, <column n>
An aggregate function being, as Ken White says, something like MIN(), MAX(), COUNT()etc. You GROUP BY allthe columns that are not aggregated.
一个聚集函数之中，肯怀特说，像MIN()，MAX()，COUNT()等你GROUP BY所有未聚合的列。
This will only work as intended if your MEMBERStable is unique on MEMBER_ID, but based on your query I suspect it is. To clarify what I mean, if your table is not unique on MEMBER_IDthen you're not counting the number of sessions per MEMBER_IDbut the number of sessions per MEMBER_IDandper MEMBER_NAME. If they're in a 1:1 relationship then it's effectively the same thing but if you can have multiple MEMBER_NAMEs per MEMBER_IDthen it's not.
如果您的MEMBERS表在 上是唯一的MEMBER_ID，这只会按预期工作，但根据您的查询，我怀疑它是。为了澄清我的意思，如果您的表不是唯一的，MEMBER_ID那么您不计算 per 的会话数，MEMBER_ID而是计算 perMEMBER_ID和per的会话数MEMBER_NAME。如果它们是 1:1 的关系，那么实际上是同一件事，但是如果您可以有多个MEMBER_NAMEs，MEMBER_ID则不是。
回答by Dennis
SELECT MEMBERS.MEMBER_ID,
MEMBERS.MEMBER_NAME,
COUNT(personal_training_sessions.session_id)
FROM MEMBERS JOIN personal_training_sessions
ON personal_training_sessions.member_id=members.member_id
GROUP BY personal_training_sessions.session_id;
You are using a COUNT function, thus the other columns, MEMBER_ID & MEMBER_NAME, must be included in the group by clause.
您正在使用 COUNT 函数，因此其他列 MEMBER_ID 和 MEMBER_NAME 必须包含在 group by 子句中。
