Oracle Having > Single Row

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 stat开发者_开发问答ement 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"

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;

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.