Re: MySQL query for average records per day

On Thu, 16 Sep 2004 16:08:45 -0400, John Holmes <holmes072000@charter.net> wrote: > Depends how you interpret his request, I guess. I took it as a request for > the count of records per day and then the average of those counts. > > So, if you had > > D1 > D1 > D1 > D1 > D2 > D2 > D3 > > The count would be > > D1 - 4 > D2 - 2 > D3 - 1 > > and the average would be (4+2+1)/3 = 2.333. Can you get that in one query? 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 * from dates; +------------+--------+ | the_date | number | +------------+--------+ | 2004-01-01 | 1 | | 2004-01-01 | 2 | | 2004-01-01 | 3 | | 2004-01-02 | 1 | | 2004-01-02 | 2 | | 2004-01-02 | 3 | | 2004-01-02 | 4 | | 2004-01-03 | 2 | | 2004-01-03 | 3 | | 2004-01-03 | 4 | | 2004-01-03 | 5 | +------------+--------+ 11 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) -- Greg Donald http://gdconsultants.com/ http://destiney.com/