ORA-00937: not a single-group group function

Question

Why I just can't use these columns in select statement? Is it necessary to use such group function GROUP BY c.CLIENT_ID,c.FNAME, c.LNAME . How to resolve this problem?

 SELECT 
          c.CLIENT_ID, c.FNAME, c.LNAME, COUNT(*) 
            FROM 
              RACE r 
                JOIN VAGON v
                  ON r.RACE_ID = v.RACE_ID
                JOIN TICKET t
                  ON v.VAGON_ID = t.VAGON_ID
                JOIN CLIENT c
                  ON c.CLIENT_ID = t.CLIENT_ID
            WHERE r.DEPART_PLACE = 'Lviv' AND r.DESTINATION_PLACE = 'Kyiv';
            GROUP BY c.CLIENT_ID
            HAVING COUNT(*) >= 2

with such GROUP BY c.CLIENT_ID,c.FNAME, c.LNAME, it still doesn't work — user3474380, Mar 30 '14 at 11:27

Patrick Hofman · Accepted Answer · 2014-03-30T11:30:16.287

0

You gave the answer yourself. Yes, you need that columns in your group by or use an aggregation function (like min or max on it.

Why?

You know the row is made unique by the Client_ID but the database doesn't. So you have to tell it to it in order to let it determine how the query is constructed and should be executed.

And you should remove the ; after 'Kyiv';

edited Mar 30 '14 at 11:30

answered Mar 30 '14 at 11:21

Patrick Hofman

153,850
22
249
325

It works. The main problem was missed semicolon – user3474380 Mar 30 '14 at 11:32
But it is strange why database doesn't know that CLIENT_ID is unique. Because it's primary key for table CLIENT. – user3474380 Mar 30 '14 at 11:34
It doesn't look that far. The statement should be self-explaining. – Patrick Hofman Mar 30 '14 at 11:35

score 0 · Answer 2 · answered Mar 30 '14 at 21:04

0

All the columns you select should be added in the group by except the expressions. Why? Because the group by work in a unique way.

answered Mar 30 '14 at 21:04

user2302449

21
1

ORA-00937: not a single-group group function

2 Answers2