[snip] mysql> desc dates; +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | the_date | date | YES | | NULL | | | number | int(11) | | | 0 | | +----------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> select the_date, count(number), avg(number) from dates group by the_date; +------------+---------------+-------------+ | the_date | count(number) | avg(number) | +------------+---------------+-------------+ | 2004-01-01 | 3 | 2.0000 | | 2004-01-02 | 4 | 2.5000 | | 2004-01-03 | 4 | 3.5000 | +------------+---------------+-------------+ 3 rows in set (0.01 sec) [/snip] But the OP didn't have a 'number', just a date so... mysql> select BilledFlag, count(BilledFlag), avg(BilledFlag) from tblClassOthers group by BilledFlag; +------------+-------------------+-----------------+ | BilledFlag | count(BilledFlag) | avg(BilledFlag) | +------------+-------------------+-----------------+ | 2003-10-07 | 251 | 2003 | | 2003-10-14 | 174 | 2003 | | 2003-10-23 | 241 | 2003 | +------------+-------------------+-----------------+ ...fails. (count is correct, avg blows up) Without a number column what is one to do? mysql> SELECT (COUNT(BilledFlag)/COUNT(DISTINCT(BilledFlag))) AS average FROM tblClassOthers; +---------+ | average | +---------+ | 222.00 | +---------+ Only gives the average. Adding any other count to this, including a SUM(IF(BilledFlag <> '', 1, 0)) [as in a crosstab] causes the (COUNT(BilledFlag)/COUNT(DISTINCT(BilledFlag))) calculation to fail, returning only the currect COUNT -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
