title: How can i get average of count in multiple tables in MySql?
body: <p>The <strong>_helper_calendar</strong> table is simple a helper table to fill date gaps with no results. So i get every date between start and end date.</p>
  
  <p>I have added explaination to each line of code so that its clear what i am trying to achieve. <strong>I am actually get back the error saying: "invalide use of group function..."</strong></p>
  
  <p>Here is my query:</p>
  
  <pre><code>   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
  </code></pre>
  
  <p>Here is a shorter query which produces the same error. Of course i need it to work in the same context like above, but maybe this helps for better understanding?</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>What am i doing wrong?</p>