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: --CELKO-- <jcelko212_at_earthlink.net>
Date: 20 Jun 2004 16:51:27 -0700
Message-ID: <18c7b3c2.0406201551.63fe67a5@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. Received on Sun Jun 20 2004 - 18:51:27 CDT

Original text of this message

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