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