RE: [PHP] MySQL query for average records per day OT
| From: | Jay Blanchard | Date: | Thu, 16 Sep 2004 20:34:19 +0000 |
| Subject: | RE: [PHP] MySQL query for average records per day OT | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-196914@lists.php.net to get a copy of this message | ||
[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