mysql获取一个月内每天的客户端平均值

Question

SELECT COUNT(client_ID) / DAY(LAST_DAY(dateRequested))
FROM `tbl_client`
WHERE dateRequested BETWEEN  DATE_FORMAT(dateRequested,'%Y-%m-01') AND LAST_DAY(dateRequested)

我想显示该月每天的平均客户数量

client_ID   |   dateRequested
   1        |   2018-07-04
   2        |   2018-07-05
   3        |   2018-07-06
   4        |   2018-07-07
   5        |   2018-08-04
   6        |   2018-08-06
   7        |   2018-08-09

我想展示

Average  |     Month
4        |   July 2018
3        |   August 2018

Answer 1

尝试以下查询:

SELECT COUNT(client_ID),concat(month(dateRequested),year(dateRequested)) 
FROM `tbl_client`
WHERE dateRequested BETWEEN  DATE_FORMAT(dateRequested,'%Y-%m-01') AND LAST_DAY(dateRequested)
group by concat(month(dateRequested),year(dateRequested))