Count occurrence

[Quoted] I have questions about the best ways to count occurrences of a value in a field. Could anyone comment on the merits of the following two SQL statements/approaches:

   select user_id, count(*) total_count ,

        COUNT(REPLACE(a.bad_flag, 'F',NULL)) bad_count    from user_comments
   group by user_id

instead of this SQL statement, which summarizes twice then joins:

   select a.user_id, a.total_count, nvl(b.bad_count,0)    from (select user_id, count(*) total_count

         from user_comments
         group by user_id)  a,
        (select user_id, count(*) total_count
         from user_comments
         where bad_flag = 'T'
         group by user_id)  b
      where b.user_id (+) = a.user_id

Also, is there a way to count the occurrence of a particular value? i.e. a syntax like this:

   SELECT COUNT(STATUS_CODE = 'BAD') from user_comments (Note: this doesn't work.)

thanks,

bill milbratz

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 13 2000 - 20:56:44 CEST