Re: how to use count(*) in "group by" queries

In message <1164123050.180204.93230_at_h54g2000cwb.googlegroups.com>, renozu <renozu_at_yahoo.com> writes
>I am trying to use count(*) to prevent queries from executing that are
>blowing out memory. The query comes in two forms:
>(1) select a, b, c, d from x, y, z where...;
>which I can count first like this:
>select count(*) from x,y,z where...; ==> easy enough
>
>The other form is:
>(2) select count(*), a, b, max(c), max(d) from x,y,z where... group by
>a,b;
>this query already has a count(*), which counts the number of rows in
>each group. How can I count the number of groups that will be returned
>without actually doing the full query?
>
>Thanks,
>Martin
>

Try

select count(*) from (select distinct a,b from x,y,z...);

However, depending on indexing, data distribution etc this could be almost as heavy as the main query.

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>