Group by: ORA-00937: not a single-group group function

Question

Why is this query not working properly?

SELECT e.* 
FROM enrolled e 
FULL OUTER JOIN student s ON e.studentid = s.sid 
WHERE ((e.quarter = 'Fall') OR (e.quarter = 'Spring')) 
GROUP BY e.studentid 
HAVING count(e.studentid) == 1;

Error that happens:

ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause: *Action: Error at Line: 1 Column: 8

Thanks,

Solution 1

As Rene said, you can't select columns which aren't in the group by clause.

If you want to select these columns but only for studentids that have count(*) = 2 for some critria you can do it like this:

select *
from
(SELECT e.*, count(*) over (partition by e.studentid)  cnt
FROM enrolled e 
FULL OUTER JOIN student s ON e.studentid = s.sid 
WHERE ((e.quarter = 'Fall') OR (e.quarter = 'Spring')) 
)
where cnt = 2

Or

SELECT e.*, count(*) over (partition by e.studentid)  cnt
FROM enrolled e 
FULL OUTER JOIN student s ON e.studentid = s.sid 
WHERE ((e.quarter = 'Fall') OR (e.quarter = 'Spring')) 
  AND (select count(*) from enrolled i where i.studentid = e.studentid) = 2

Anyway, you can't do == only =

Solution 2

When you use "group by" you can only select the columns that are used in the "group by".

You are selecting e.*. change that to e.studentid.

And its having count(*) = 1. Not having count(*) ==1.

Comments

[Sohel Mansuri]

it is giving me a error saying "missing expression"

[Stefan Brendle]

You're using ==? Are you sure? Not just a single one? Can you tell us the error message from sql server? By the way I would do the part in the where-statement with the IN operator ... WHERE e.quarter IN ('Fall','Spring')

[Sohel Mansuri]

I am doing what I posted in the question. Here is what it says: "ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause: *Action: Error at Line: 1 Column: 8"