1

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,

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
Sohel Mansuri
  • 564
  • 2
  • 6
  • 18
  • it is giving me a error saying "missing expression" – Sohel Mansuri Apr 25 '13 at 05:06
  • 2
    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')` – Stefan Brendle Apr 25 '13 at 05:07
  • 1
    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" – Sohel Mansuri Apr 25 '13 at 05:09

2 Answers2

4

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 =

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
1

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.

Rene
  • 10,391
  • 5
  • 33
  • 46