Re: Counting propositions

• Post for FREE via your newsreader at post.usenet.com ****

"--CELKO--" <jcelko212_at_earthlink.net> wrote in message news:18c7b3c2.0406201551.63fe67a5_at_posting.google.com...
> >> What is the difference between COUNT(*) and COUNT(<exp>) ? <<
>
> COUNT(*) is the table's cardinality; rows are counted, even when they
> have NULLs. The COUNT([DISTINCT] <exp>) computes the <exp> and builds
> a one-column working table. NULLs are removed, then if there is a
> DISTINCT option, redundant duplicates are removed. Finally, the
> aggregate function is performed on the column and a scalar result is
> returned.
>
> Thus
> COUNT (salary)
> COUNT (salary + commission)
> can surpise you when "commission IS NULL" for non-sales personnel;
> the expression (salary + commission) will be dropped. You might have
> meant
> COUNT (salary + COALESCE(commission, 0.00).
>
> you can also use SUM(CASE WHEN <pred> THEN 1 ELSE 0 END) to do a lot
> of work in aq single query.

Then the biggest difference between COUNT(*) and COUNT(<exp>) is that - COUNT(*) does count NULLs (including all NULL rows :-) - COUNT(<exp>) does not count NULLs

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=