How to calculate average of count per day 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 per 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.