Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Counting propositions

Re: Counting propositions

From: x <x-false_at_yahoo.com>
Date: Mon, 21 Jun 2004 18:29:07 +0300
Message-ID: <40d6fdc2$1@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

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

Received on Mon Jun 21 2004 - 10:29:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US