Re: Count occurrence

Use sum(decode(bad_flag='F'),0,1) in statement 1 and you won't need statement 2.
Statement 3 is not proper syntax as you could have easily verified from the docs. You need either to include a where clause or to use the construct above.

Regards,

Sybrand Bakker, Oracle DBA

<billmil_at_my-deja.com> wrote in message news:8s7lta$35a$1_at_nnrp1.deja.com...
> 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 Sun Oct 15 2000 - 07:47:37 CEST