Trying to select data and then do a average on the selected data in mariadb

Question

SELECT counts FROM (SELECT COUNT(ch_id) AS counts FROM tbl_warrants_checked WHERE status = "active" GROUP BY dateChecked);

Answer 1

This uses MariaDB 10.5, but should work with any version since ~10.2.2.

If we assume every day has data, or we don't care about the days which have no data, the following will calculate the average checks per day since 1 month ago (the last month of data):

-- Average of counts per day since ~1 month ago.
-- Only for days present in the set
SELECT AVG(counts) AS average
  FROM (
          SELECT COUNT(ch_id) AS counts
            FROM tbl_warrants_checked
           WHERE status = 'active'
             AND dateChecked > (current_date - INTERVAL '1' MONTH)
           GROUP BY dateChecked
       ) AS x
;

Sample result:

+---------+
| average |
+---------+
|  3.2941 |
+---------+

If we wish to account for missing days and treat them as 0, the following generates the days since 1 month ago and LEFT JOINs that with the found counts per day:

-- Average of counts per day since ~1 month ago.
-- Treat missing days as 0
WITH RECURSIVE dates (date) AS (
          SELECT current_date UNION ALL
          SELECT date - INTERVAL '1' DAY FROM dates
           WHERE date > (current_date - INTERVAL '1' MONTH)
     )
SELECT AVG(COALESCE(counts, 0)) AS average
  FROM dates AS d
  LEFT JOIN (
          SELECT dateChecked
               , COUNT(ch_id) AS counts
            FROM tbl_warrants_checked
           WHERE status = 'active'
             AND dateChecked > (current_date - INTERVAL '1' MONTH)
           GROUP BY dateChecked
       ) AS x
    ON d.date = x.dateChecked
;

Sample result:

+---------+
| average |
+---------+
|  1.7500 |
+---------+

Working test case with data which produces the above results

Note: We could also have calculated the missing days and used that in the final calculation without the recursion and JOIN. There's another simplification available as well.