Oracle Having > Single Row

Question

I have an Oracle query, which has something to the effect of

Having Count(field) > (Long SQL statement that returns one row) Both sides of the query work alone, but together I get a "not a group by" expression.

When replacing the long SQL statement with a number it works, but I assumed the two were equivalent if only one row is returned?

Edit After doing some playing around I realized: ... Table T ... Having Count(field) > (Long SQL statement with Table A Where A.field = T.field) It works when I replace T.field with any of the specific options for T.field, but when I reference T.field specifically I get the same "not a group by expression"

Can you post your query? This works for me `SELECT DUMMY FROM dual GROUP BY DUMMY HAVING COUNT(DUMMY ) > (select 1 from dual);` — Martin Smith, Oct 16 '11 at 23:37
I can't post my exact query, but I realize I might have been looking at the wrong part of it for the problem @Martin — Shane Chin, Oct 16 '11 at 23:50
Solved. My pre > statement apparently needs to have T.field within the group by. I'm not sure why the worked alone but not in conjunction with one another. — Shane Chin, Oct 16 '11 at 23:58
If you posted examples of what worked and what did not (with fake field names, not your original query), someone could explain. — ypercubeᵀᴹ, Oct 17 '11 at 00:23

score 1 · Answer 1 · edited Oct 16 '11 at 23:55

1

When Oracle parses your query it doesn't know if the query is going to return only one row or a bunch of rows. So simply append group by your_column to the end of your query.

For example this query returns one row:

select count(*) from user_objects;

But if I wanted to include sysdate along with that, I would have to do

select 
    sysdate the_date, 
    count(*) 
from
    user_objects 
group by 
    the_date;

edited Oct 16 '11 at 23:55

Conrad Frix

51,984
12
96
155

answered Oct 16 '11 at 23:39

RichardJQ

173
3

1

I formatted your code for you. This can be done by selecting your code and pressing the `{}` button. – Conrad Frix Oct 16 '11 at 23:59

score 0 · Accepted Answer · answered Oct 17 '11 at 00:30

SELECT ... 
FROM Table T ...
GROUP BY T.afield
HAVING Count(T.anotherfield) 
       > (Long SQL statement with Table A Where A.somefield = T.afield)

should work ok.

SELECT ... 
FROM Table T ...
GROUP BY T.anotherfield
HAVING Count(T.anotherfield) 
       > (Long SQL statement with Table A WHERE A.somefield = T.afield)

should not work. A field (like T.afield) that is not included in the GROUP BY list, cannot be referenced in SELECT, HAVING or ORDER BY clauses. Only aggregate functions of that field can be referenced - you could have WHERE A.somefield = MIN(T.afield) for example.

Oracle Having > Single Row

2 Answers2