Question 113224 in Mysql Question posted in
The official MySQL documentation can be found here.

Average of last 3 months against a max date in MySQL

My data looks like this:

And the output that I am looking for is against every hhid, I want to calculate the average of the last 3 months of ttlasset and ttlimcome and the date(frdate) should be the max date.
So for every hhid, on max date,I want to calculate the average of last 3 months of ttlasset and ttlincome if the current month is not complete.
SO output should be like this:
enter image description here

This is the code that I used:

SELECT hhid, MAX(frdate), AVG(ttlincome),AVG(ttlasset)
FROM hhmonthlyprogress WHERE MONTH(frdate) = (SELECT MONTH(NOW())-1) OR MONTH(frdate) = (SELECT MONTH(NOW())-2) OR MONTH(frdate) = (SELECT MONTH(NOW())-3)
    FROM hhmonthlyprogress
Tags:

2

Answers


  1. 0

    Hello Please check my SQL Code: I used CTE to solve it:

    WITH cte AS(SELECT hhid, MAX(date) AS MaxDate, IF(MONTH(MAX(date)) = MONTH(CURRENT_DATE()),MAX(date) - INTERVAL 4 MONTH,MAX(date) - INTERVAL 3 MONTH) AS LowerLimit FROM YourTable GROUP BY hhid)SELECT cte.hhid, MaxDate, AVG(ttlasset) AS avg_asset, AVG(ttlincome) AS avg_income FROM cte JOIN YourTable ON cte.hhid = YourTable.hhid WHERE date <= MaxDate AND date >= LowerLimit GROUP BY cte.hhid;

    And The result It produces:

    | hhid | MaxDate    | avg_asset  | avg_income |
+------+------------+------------+------------+
|    2 | 2022-09-15 | 34464.0000 |  4650.6667 |
|    3 | 2022-07-06 |     0.0000 |     0.0000 |
|    4 | 2022-09-08 |     0.0000 |     0.0000 |
|    5 | 2022-10-09 | 21850.0000 |  3095.0000 |
+------+------------+------------+------------+
4 rows in set (0.01 sec)
    Login or Signup to reply.
  2. 0

    step 1 : finding max date for each hhid

    SELECT hhid, MAX(DATE(frdate)) Maxdate
  FROM hhmonthlyprogress
GROUP BY hhid;

    step 2 : using the query from step 1 as derived table and join with the hhmonthlyprogress table.

    SELECT * 
  FROM hhmonthlyprogress ha
  JOIN 
(SELECT hhid, MAX(DATE(frdate)) Maxdate
  FROM hhmonthlyprogress
GROUP BY hhid) hb
  ON ha.hhid=hb.hhid;

    step 3: adding WHERE condition

    SELECT * 
  FROM hhmonthlyprogress ha
  JOIN 
(SELECT hhid, MAX(DATE(frdate)) Maxdate
  FROM hhmonthlyprogress
GROUP BY hhid) hb
  ON ha.hhid=hb.hhid
  WHERE DATE(frdate) <= LAST_DAY(Maxdate - INTERVAL 1 MONTH)
  AND DATE(frdate) >= LAST_DAY(Maxdate - INTERVAL 4 MONTH)+INTERVAL 1 DAY;

    Explanation for WHERE condition

    LAST_DAY  (  Maxdate - INTERVAL 1 MONTH  )
 ^^          |  ^^^
returns last | returns previous month date based
day of the   | on Maxdate value.
month.       |

    The second condition is basically the same but since there’s no FIRST_DAY() function in MySQL that I know of, then I use LAST_DAY() from Maxdate value with 4 month previous interval then add INTERVAL 1 DAY to get the first day of next month after that.

    step 4: Finalize query with AVG():

    SELECT ha.hhid,
       Maxdate,
       SUM(ttlasset)/3,
       SUM(ttlincome)/3
  FROM hhmonthlyprogress ha
  JOIN 
(SELECT hhid, MAX(DATE(frdate)) Maxdate
  FROM hhmonthlyprogress
GROUP BY hhid) hb
  ON ha.hhid=hb.hhid
  AND DATE(frdate) <= LAST_DAY(Maxdate - INTERVAL 1 MONTH)
  AND DATE(frdate) >= LAST_DAY(Maxdate - INTERVAL 4 MONTH)+INTERVAL 1 DAY
GROUP BY ha.hhid;

    Note: I’ve changed AVG() to SUM() then divide by 3 – as a representation of 3 months.

    Here is updated demo fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search