Re: Counting propositions

>> 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 Mon Jun 21 2004 - 01:51:27 CEST