At 04:21 PM 23-11-02 -0500, Ann H wrote:

>At 10:33 PM 11/22/2002 +1100, Helen Borrie wrote:
>
> >Select sum("Col_1") as Total, count("Col_1") AS answer
> > where "col_2" = '2';
>
>There is a subtle, but sometimes important, difference
>between count (<column name>) and count (*). The first
>counts only those rows where the column value is not
>null. The second counts all matching rows.

Good point: and perhaps I should also have mentioned that SUM() returns
NULL if any of the rows being totalled in the aggregate set contain NULL in
the column being operated on.

Select
sum("Col_1") as Total,
count("Col_1") AS answer
where "col_2" = '2'
and "Col_1" is not null;

-- solves the problem. In this case, count ("Col_1") and count(*) would
deliver the same set.

But not all aggregating functions are created equal. AVG() will simply
jump over NULL in the column being aggregated. Then it becomes rather
important to return the number of rows appropriately for requirements.

Select
AVG("Col_1") as Total,
count(*) AS answer
where "col_2" = '2';
-- counts all rows in the aggregate and is the same as the divisor of the
averaging calculation;

whereas

Select
AVG("Col_1") as Total,
count("Col_1") AS answer
where "col_2" = '2';
-- will not include the null rows in the count. The count is therefore not
the same as the divisor in the averaging calculation.

heLen