Re: SQL CORR SUBQUERY EXPERTS

MGreen1962 wrote:
>
> Hello, 2 all that would try to answer this question.
>
> How would I write a query that returns group rows where the count >= some
> value.
>
> i.e.
> select table_name,count(*)
> from sys.dba_constraints
> where count(*) >= 7
> group by table_name;
>
> I know the count(*) function cannot be used in a where clause. I tried to
> use a correlated subquery and my system locked each time. My query is as
> follows:
>
> select table_name,count(*) from sys.dba_constraints x
> where to_number('1')>=(select count(*) from sys.dba_constraints where
> x.table_name=table_name)
> group by table_name;
>
> Please help. email Mgreen1962_at_aol.com

You're right that you can't use aggregate functions (like COUNT) in a where clause. You can, however, use them in a HAVING clause (which you can only use if you have a GROUP BY clause). The HAVING cause works just like the WHERE clause, except it filters the records after the GROUP BY takes place, so it allows the aggregate functions.

To do you statement using the HAVING clause you could use:

	SELECT table_name, count(*)
	FROM sys.dba_constraints
	GROUP BY table_name
	HAVING count(*) >= 7;	

This should give you the name and number of constraints for each table that has seven or more constraints.

-- 
-------------------------------------------------
Ken Johnson -  Senior Technical Consultant
Tapestry Computing, Inc. http://www.tapestry.com