Re: SQL statement question

In article <8p8gvn$8r$1_at_news.sinet.slb.com>,   "Yong Huang" <yhuang_at_indigopool.com> wrote:
> Should be the outer count().
>
> BTW, what's the use of this query? It always returns 1 anyway.
>
> Yong Huang
> yhuang_at_indigopool.com
>
> Jimmy <anonymous_at_anonymous.com> wrote in message
> news:39B87462.9749954A_at_anonymous.com...
> > Hello all,
> >
> >
> > THe following SQL statement:
> >
> > select count(count(*)) from table_a group by field_a having count(*)
 

> > 1
> >
> >
> > Is the count(*) in the having part refer to the inner count(*) (or
> > outer count(*)) in the select part? Maybe the answer can be known by
> > testing, however, could anyone explain how Oracle resolve the above
> > query?
> >
> > Thanks,
> > Jimmy
>
>

Unfortunately your assessment of the query is faulty. The query does not always return 1:

select count(count(*)) from table_a group by field_a having count(*) >1

The count(*) in the having clause refers to the INNER count, NOT the outer count as one might presume. As such, the query does NOT return 1 but returns the count of the records returned that have a count(*) value greater than 1. As an example:

Let us presume we have a table of water meter readings:

 READINGS

 METER_NO      varchar2(12)
 RDG_DT        date
 RDG_VAL       number

In this table there will be a number of records for each meter number. Rewriting the above query to access this table we have:

select count(count(*))
from readings
group by meter_no
having count(*) > 1;

First ORACLE will select the count(*) for each group of meter_no values:

select count(*)
from readings
group by meter_no
having count(*) > 1;

Listing a possible outcome of the above query we have:

COUNT(*)

     177
     234
     567
      44
       3
      69

ORACLE will then select the count(*) of those records. The query actually executes as though it were written thus:

select count(*)
from (select count(*)

      from readings
      group by meter_no
      having count(*) > 1);

From the above result set it can be seen that the result of the entire query would be 6, NOT 1.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.