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

Mysql – Sum by month cumulatively, 2 variables

I have two columns: wash score (wash_sc) and agric score (agric_sc). I want to sum both cumulatively and group by month.

Currently am able to accomplish this for one variable, though it is takes a long time to return the response:

select date_format(s1.createdAt,'%b-%Y') as month, 
    (
       select AVG(s2.wash_sc) 
       from checkins s2 
       where s2.createdAt <= last_day(s1.createdAt)
    ) as total 
from checkins s1 
group by month 
order by s1.createdAt;

How can I do this (faster!) for both columns?

enter image description here

Tags:

2

Answers


  1. 0 
    SELECT 
    DATE_FORMAT(s1.createdAt, '%b-%Y') AS month,
    (SELECT SUM(s2.wash_score) FROM checkins s2 WHERE MONTH(s2.createdAt) <= MONTH(s1.createdAt)) AS cumulative_wash_score,
    (SELECT SUM(s3.agric_score) FROM checkins s3 WHERE MONTH(s3.createdAt) <= MONTH(s1.createdAt)) AS cumulative_agric_score
FROM 
    checkins s1
GROUP BY 
    MONTH(s1.createdAt)
ORDER BY 
    s1.createdAt;
    Login or Signup to reply.
  2. 0

    You can do it using the window function AVG() :

    with cte as (
  select *, AVG(wash_sc) over (partition by MONTH(createdAt) order by createdAt) as cumul_wash_sc,
            AVG(agric_sc) over (partition by MONTH(createdAt) order by createdAt) as cumul_agric_sc
  from checkins
)
select date_format(createdAt,'%b-%Y') as month, max(cumul_wash_sc) as cumulative_wash_score, max(cumul_agric_sc) as cumulative_agric_score
from cte
group by date_format(createdAt,'%b-%Y')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search