mysql - 如何获得MySql中多个表的计数平均值？

Question

_helper_calendar 表是一个简单的辅助表，用于填充没有结果的日期空白。所以我得到了开始日期和结束日期之间的每个日期。

我已经为每一行代码添加了解释，以便清楚地表明我想要实现的目标。 我实际上收到了错误消息:“组函数的使用无效......”

这是我的查询:

   SELECT 
    /*get the day date*/
    cal.datefield AS cal_date, 
    /*get appointments by date from user*/
    (SELECT COUNT(e1.id) FROM events_table e1 WHERE DATE(e1.start) = DATE(cal.datefield) AND e1.user_id = 1 ) AS appointments, 
     /*get sales by date from user*/
    (SELECT COUNT(s1.id) FROM sales_table s1 WHERE DATE(s1.created) = DATE(cal.datefield) AND s1.user_id  = 1) AS sales,
       /*get average appointments by date from all users*/
    (SELECT COUNT(e2.id) FROM events_table e2 WHERE DATE(e2.start) = DATE(cal.datefield) ) AS appointments_avg, 
      /*get average sales by date from all users*/
    (SELECT AVG(COUNT(s2.id)) FROM sales_table s2 WHERE DATE(s2.created) = DATE(cal.datefield)) AS sales_avg
FROM events_table e 
    RIGHT JOIN _helper_calendar AS cal ON (DATE(e.start) = cal.datefield)
WHERE DATE(cal.datefield) BETWEEN '2018-06-01' AND '2018-07-31' 
GROUP BY cal_date

这是一个较短的查询，它会产生相同的错误。当然，我需要它在与上面相同的上下文中工作，但这也许有助于更好地理解？

SELECT s.start, AVG(COUNT(s.id)) s2_cnt FROM sales_table s WHERE DATE(s.start) BETWEEN '2018-06-01' AND '2018-07-31'  GROUP BY DATE(s.start);

我做错了什么？

Answer 1

使用从日历表到其他表的左联接并使用按日期分组的聚合函数重写查询:

SELECT
    cal.datefield AS cal_date,
    COUNT(e1.id) AS appointments, 
    COUNT(s1.id) AS sales
FROM _helper_calendar AS cal
LEFT JOIN events_table e ON DATE(e.start) = cal.datefield
LEFT JOIN events_table e1 ON DATE(e1.start) = DATE(cal.datefield)
   AND e1.user_id = 1
LEFT JOIN salestable ON DATE(s1.created) = DATE(cal.datefield)
   AND s1.user_id = 1
WHERE DATE(cal.datefield) BETWEEN '2018-06-01' AND '2018-07-31'
GROUP BY cal.datefield

并将此查询连接到另一个查询，该查询获取所有用户在加入日期的每个日期的平均值。