avg最多用多少列 mysql_按列分组时,MYSQL COUNT结果和AVG结果

我试图选择过去30天每天的平均完成次数,然后取其平均值.这是我的Rundown表的示例

RundownID | WorkOrderID | ForemanID | Completion | RundownDate

1 | 1 | 1 | 1 | 2017-10-27

2 | 2 | 1 | 1 | 2017-10-26

3 | 3 | 1 | 1 | 2017-10-20

4 | 4 | 1 | 1 | 2017-10-27

5 | 5 | 2 | 1 | 2017-10-27

6 | 6 | 2 | 1 | 2017-10-27

7 | 7 | 3 | 1 | 2017-10-25

8 | 8 | 2 | 0 | 2017-10-20

我正在寻找的结果是

ForemanID | ForemanAvg

1 | 4

2 | 2

3 | 1

它应采用过去30天内的完成行总数,并平均每个唯一ForemanID每天的总数.然后平均每天的总和,以得出过去30天的平均值

我知道我已经接近了,但是我似乎无法正确配置它.这是我走多远

SELECT

ForemanID,

COUNT(RundownDate) AS ForemanAvg

FROM Rundown

WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))

AND Completion = 1

GROUP BY ForemanID

结果

ForemanID | ForemanAvg

23 | 70

24 | 100

25 | 100

26 | 76

27 | 109

29 | 11

编辑：

我越来越近了.使用以下查询,我得到了想要的结果,但是由于某种原因,它只给我一个ForemanID

SELECT

a.ForemanID,

AVG(a.ForemanAvg)

FROM (SELECT

RundownDate,

ForemanID,

COUNT(RundownDate) AS ForemanAvg

FROM Rundown

WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))

AND Completion = 1

GROUP BY ForemanID, RundownDate ) AS a

解决方法:

以下查询应该工作：

SELECT

ForemanID,

count(*)/COUNT(distinct RundownDate) AS ForemanAvg

FROM Rundown

WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))

AND Completion = 1

GROUP BY ForemanID;

希望能帮助到你！

标签：mysql

来源： https://codeday.me/bug/20191110/2015365.html