不是GROUP BY表达式错误 [英] Not a GROUP BY expression error

问题描述

我对数据库相对来说比较陌生。我正在使用Oracle，我试图执行这个查询来查找成员所拥有的个人培训课程的数量。

这些表格是;

会员

  MEMBERS_ID（NUMBER），$ b $ MEMBER_NAME（VARCHAR），
 MEMBER_PHONE（VARCHAR），
 MEMBER_EMAIL（VARCHAR）
  ADDRESS_ID（NUMBER），CLUB_ID（NUMBER）
 MEMBER_NAME / code> 

PERSONAL_TRAINING_SESSIONS



  SESSION_ID（VARHCAR），
 MEMBER_ID（NUMBER），
 STAFF_ID（VARCHAR），
 SESSION_DATETIME（日期）
  pre> 
 
 
我的查询正在回应这个错误： 
 
 
 
 
 ORA-00979：not一个GROUP BY表达式
 00979. 00000  - 不是一个GROUP BY表达式
 *原因：
 
 *操作：行错误：1列：8 
 
 
 
 
 
  SELECT MEMBERS.MEMBER_ID，MEMBERS.MEMBER_NAME，COUNT（personal_training_session s.session_id）
从成员加入personal_training_sessions 
 ON personal_training_sessions.member_id = members.member_id 
 GROUP BY personal_training_sessions.session_id; 
  
任何人都可以指向正确的方向吗？我看了看周围是否需要单独计数查询？
解决方案
错误说明了这一切，您不是按 MEMBERS.MEMBER_ID 和 MEMBERS.MEMBER_NAME 。
  SELECT MEMBERS.MEMBER_ID，MEMBERS.MEMBER_NAME 
，COUNT（personal_training_sessions.session_id）
来自会员
加入personal_training_sessions 
 ON personal_training_sessions.member_id =会员。 member_id 
 GROUP BY MEMBERS.MEMBER_ID，MEMBERS.MEMBER_NAME 
  
您希望个人会话的数量每个成员，因此您需要按成员信息进行分组。 
 
 
 
基本的（当然它可以得到更复杂的）GROUP BY，SELECT查询是：
 
 
  SELECT< column 1>，< column n> 
，<合计函数1>，<合计函数n> 
 FROM< table_name> 
 GROUP BY< column 1>，< column n> 
  
像Ken White说的那样，一个集合函数像 MIN（） ， MAX（）， COUNT（）等GROUP BY 全部 
 
 
这只会在您的 MEMBERS 表为<在 MEMBER_ID 上是唯一的，但根据您的查询我怀疑它是。为了澄清我的意思，如果你的表在 MEMBER_ID 上不是唯一的，那么你不计算每个 MEMBER_ID ，但每个 MEMBER_ID  和每个 MEMBER_NAME 的会话数。如果他们处于1：1的关系，那么它实际上是同样的事情，但如果你可以有多个 MEMBER_NAME  s  MEMBER_ID 那么它不是。
 
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.

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


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?
解决方案
The error says it all, you're not grouping by MEMBERS.MEMBER_ID and 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 per member, 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:
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 all the columns that are not aggregated.

This will only work as intended if your MEMBERS table 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_ID then you're not counting the number of sessions per MEMBER_ID but the number of sessions per MEMBER_ID and per 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_ID then it's not.

                        
这篇关于不是GROUP BY表达式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！