How to work out average of count between dates in MYSQL?-CodePudding

I have a table called SDON, and a query that counts the total number of pallets on each date of import. I wish to group these together and average the counts across the month.

My count query:

SELECT COUNT(`Storage unit`) AS `Pallets`,`import_date` 
from `SDON` 
GROUP BY `import_date`"

I wish for the following:

CodePudding user response：

You can group the results of your original query by year and month of the date:

SELECT EXTRACT(YEAR_MONTH FROM import_date) AS import_month
     , AVG(day_total) AS average_per_day
FROM (
    SELECT import_date
         , COUNT(`storage unit`) AS day_total
    FROM sdon
    GROUP BY import_date
) AS x
GROUP BY EXTRACT(YEAR_MONTH FROM import_date)

Converting a number such as 202207 to 2022-07 is trivial.