Mysql 对 count 的所有结果进行平均

Question

我试图对“计数”列中的所有结果进行平均，但它告诉我“计数”列不存在。

SELECT
    Date,
    COUNT(Date) as Count,
    a.Average
From Rundown
LEFT JOIN (
            SELECT AVG(Count) AS Average, WorkOrder FROM Rundown
            ) AS a
            ON (Rundown.WorkOrder = a.WorkOrder)
WHERE Rundown.Date > (DATE_SUB(CURDATE(), INTERVAL 30 Day))
AND Completion = 1
GROUP BY Date

我期待的结果

Date | Count | Average
2016-09-08 | 19 | 19
2016-09-09 | 20 | 19
2016-09-10 | 19 | 19
2016-09-12 | 17 | 19

编辑:

我最终编写了第二个查询来为我完成此任务

SELECT 
    ROUND(avg(a.Count)) as Average
FROM (
SELECT
    Date,
    COUNT(Date) as Count
From Rundown
    WHERE Rundown.Date > (DATE_SUB(CURDATE(), INTERVAL 7 Day))
    AND Completion = 1
GROUP BY Date
    ) as a

Answer 1

您无法计算任何地方的每个日期。所以你可以这样计算。

 SELECT COUNT(*) / COUNT(DISTINCT date) AS Average, WorkOrder 
 FROM Rundown
 GROUP BY WorkOrder