CODEWITHSUNDEEP
mysqlsqlaverageaggregate-functions
James
James

Reputation: 63

How to calculate average of count per day in MySQL?

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`

query result

I wish for the following:

Average Pallets | Import Month 18500

Upvotes: 0

Views: 562

Answers (2)

Rick James
Rick James

Reputation: 142218

SELECT  AVG(Pallets) AS AveragePallets,
        DATE_FORMAT(import_date, '%b-%y') AS ImportMonth
    FROM sdon
    GROUP BY LEFT(import_date, 7)
    ORDER BY LEFT(import_date, 7);

(I don't see any need for a subquery.)

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272046

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.

Upvotes: 4

Related Questions