Not a GROUP BY expression error

Question

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?

I would highly recommend going through a tutorial and having a play with some selects to see what is possible. — Ben, Dec 19 '12 at 23:14

Ben · Accepted Answer · 2012-12-19T23:25:31.643

6

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.

edited Dec 19 '12 at 23:25

answered Dec 19 '12 at 23:10

Ben

51,770
36
127
149

2

+1. To clarify, the `GROUP BY` should contain all the columns that are not aggregated (not part of a `SUM()`, `AVG()`, `MIN()`, `MAX()`, or other aggregate function). – Ken White Dec 19 '12 at 23:13
I've just added a little thing like that @KenWhite :-). – Ben Dec 19 '12 at 23:14
I see that, but I'm not sure that it clearly explains; someone new to SQL might not know that `, ` means `all the columns that are not aggregated`. – Ken White Dec 19 '12 at 23:17
Another tip: if you find that you have a great many elements in the GROUP by clause in a query like this (for example you have a lot of columns in the MEMBERS table that you are including) then you could place the aggregation of the personal_training_session data in an inline view or common table expression, grouping by member_id, and then join this pre-aggregated result to the members table. This can be much faster with large data sets, particularly if your instance is tuned for OLTP queries with a low PGA memory allocation, as it can save the sort from spilling to disk. – David Aldridge Dec 20 '12 at 07:34
Thank you for making me smile @DavidAldridge, this is why I love SO! – Ben Dec 20 '12 at 08:45

score 2 · Answer 2 · answered Dec 19 '12 at 23:10

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.

Not a GROUP BY expression error

2 Answers2

Linked