Group by on single column is not working

Question

When I use following query it works:

select d.id, 
       d.name, 
       count(e.id) as numberofemployees
  from department d, 
       employee1 e
 where d.id=e.deptid
group by d.id, 
         d.name
order by d.id;

But when I use following query it gives me error:

select d.id, 
       d.name, 
       count(e.id) as numberofemployees
  from department d, 
       employee1 e
 where d.id=e.deptid
group by d.id 
order by d.id;

Error is as follows:

select d.id, d.name, count(e.id) as numberofemployees

ERROR at line 1: ORA-00979: not a GROUP BY expression

I don't understand what the problem is. I think grouping on single column should be fine. Any help will be appreciated.

There is literally no difference between those two queries. Did you copy and paste the wrong query for your non working one? — Mason T., Feb 26 '14 at 15:24
You've made a mistake and pasted the same query twice. The one generating that error must be different; neither of the ones you've included would cause that error to appear. — Ken White, Feb 26 '14 at 15:31

score 6 · Answer 1 · answered Feb 26 '14 at 15:25

6

In most DBMS implementations, GROUP BY must include every column that does not appear in an aggregate function (AVG(), SUM(), COUNT(), etc.). If you have two columns (ID and Name) that are not in aggregate functions, you must list both of them in your GROUP BY clause.

answered Feb 26 '14 at 15:25

Ken White

123,280
14
225
444

Upvoted, no idea why this was downvoted. Seems like the obvious correct answer, even though the OP posted an incorrect error query. – Tobberoth Feb 26 '14 at 15:27
@Tobberoth: Me, either. With the exception of MySQL (and maybe a couple of others), this is true for every DBMS I'm familiar with (and Oracle in particular). Thanks. :-) – Ken White Feb 26 '14 at 15:28
and why my answer gets downvoted? it's true too. *cry cry* :P Btw, upvoted for nice explanation. – Fabian Bigler Feb 26 '14 at 15:29
@Fabian: Why are you asking me? I didn't downvote it, and I have no idea why anyone did so. – Ken White Feb 26 '14 at 15:29
@KenWhite There are two tables in my query employee1 and department. I am using e.id in aggreagate function. You said "GROUP BY must include every column that does not appear in an aggregate function" but every column of which table employee1 or department? – Sachin Feb 27 '14 at 06:01
**Every column** that appears in the SELECT statement that is not part of an aggregate, **regardless of which table it's from**, must appear in the GROUP BY expression. – Ken White Feb 27 '14 at 13:31

Fabian Bigler · Answer 2 · 2014-02-26T15:31:43.190

2

In the second query the column name is not in the group by clause, which is causing your error.

So the following query would yield a result:

select d.id,
       count(e.id) as numberofemployees
  from department d, 
       employee1 e
 where d.id=e.deptid
group by d.id
order by d.id;

Ken White's answer explains well why this is the case.

edited Feb 26 '14 at 15:31

answered Feb 26 '14 at 15:25

Fabian Bigler

10,403
6
47
70

Group by on single column is not working

2 Answers2