Getting average value based on grouped data

I’m trying to find the average of net total for a given month, based on previous years to help show things like seasonal trends in sales.

I have a table called “Invoice” which looks similar to the below (slimmed down for the purpose of this post):

ID – int
IssueDate – DATE
NetTotal – Decimal
Status – Enum

The data I’m trying to get, for example would be similar to this:

(sum of invoices in June 2018 + sum of invoices in June 2019 + sum of invoices in June 2020) divided by number of years covered (3) = Overall average for June

But, doing this for the full 12 months of the year based on all the data (not just 2018 through to 2020).

I’m a bit stumped on how to pull this data. I’ve tried subqueries and even tried using a SUM within an AVG select, but the query either fails or returns incorrect data.

An example of what I’ve tried:

SELECT MONTHNAME(`Invoice`.`IssueDate`) AS `CalendarMonth`, AVG(`subtotal`)
FROM (SELECT SUM(`Invoice`.`NetTotal`) AS `subtotal`
        FROM `Invoice`
        GROUP BY EXTRACT(YEAR_MONTH FROM `Invoice`.`IssueDate`)) AS `sub`, `Invoice`
GROUP BY MONTH(`Invoice`.`IssueDate`)

which returns:

I see two parts to this query, but unsure how to structure it:

A sum and count of all data based on the month
An average based on the number of years

I’m not sure where to go from here and would appreciate any pointers.

Ideally, I’d want to get the totals from rows where “Status” = “Paid”, but trying to crack the first part first. Walk before running as they say!

Any guidance greatly appreciated!

Answer

Basically you want two levels of aggregation:

SELECT mm,  AVG(month_total)
FROM (SELECT YEAR(i.IssueDate) as yyyy, MONTH(i.issueDate) as mm,
             SUM(i.`NetTotal`) as month_total
      FROM Invoice i
      GROUP BY yyyy, mm
     ) ym
GROUP BY mm;

Advertisement

Answer