Re: count(*) in sql statement

In article 94Jun4121002_at_tikal.shell.com, blain_at_shell.com (Philip C. Blain) writes:
>select count(distinct a.d), count(distinct b.d)
>from abc a, abc b
>where a.d between '15-jan-47' and '15-may-92'
>and b.d between '16-may-92' and '21-may-94'
>
>Note also that your solution ignores the
>case of multiple rows with the same date (if the date is truncated).
>

certainly true.

if there will be non-unique dates, as almost certainly there will be, one has to use a set of DISTINCT column entries, even if one has to stoop to using rowid.

select count ( distinct a.rowid ) a_count, count ( distinct b.rowid ) b_count ...
returns the correct result. yet, rowid has a bad reputation and may be frowned on in your organization.

the main advantage, i see, to this method, as opposed to the one given by mr. blain, is that it doesn't hurt my head. <smiley face>

mr. blain`s method, as i understand it, trades computation for database access and is, thus, superior for many applications.

there you go
//michael

::
:: michael ryan
:: ryan_at_xsoft.xerox.com
:: 415 813 7620
:: ob disclaimer: opinions expressed are not those of xerox, corp.