CODEWITHSUNDEEP
mysqlsqldatabase
UTKRISHT AGRAWAL
UTKRISHT AGRAWAL

Reputation: 43

SQL query to get total monthly amount

id name startDate endDate amount amountPerDay
1 abc 03-02-2022 08-05-2022 1100 11.7
2 def 16-02-2022 21-05-2022 1650 17.55
3 abc 08-05-2022 08-06-2022 500 16.12

Query to get the following output

Output

Month TotalAmount
January 0
February 503.1
March 906.75
April 877.5
May 832.91
June 129.6

... so on

Upvotes: 1

Views: 52

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

You can create a calender using a recursive cte. Then you can sum the daily amount's per month:

with recursive cal (d) as (
    select min(startdate) as d from t
    union all 
    select date_add(d, interval 1 day) from cal where d<(select max(enddate) from t)
)
select monthname(cal.d), sum(t.amountperday)
from cal
join t 
    on cal.d between t.startdate and t.enddate
group by monthname(cal.d)

monthname(cal.d)    sum(t.amountperday)
May                 849.03
April               877.50
March               906.75
February            532.35
June                128.96

Fiddle

If you want to include "0" months, extend your calendar and use a left join:

with recursive cal (d) as (
    select '2022-01-01'
    union all 
    select date_add(d, interval 1 day) from cal where d<'2022-12-31'
)
select monthname(cal.d), coalesce(sum(t.amountperday),0)
from cal
left join t 
    on cal.d between t.startdate and t.enddate
group by monthname(cal.d);

monthname(cal.d)    coalesce(sum(t.amountperday),0)
January     0.00
February    532.35
March       906.75
April       877.50
May         849.03
June        128.96
July        0.00
August      0.00
September   0.00
October     0.00
November    0.00
December    0.00

If you are doing such calculations on a regular basis it is probably a good idea to create a calendar table. Additional information such as working holidays etc. can be kept there

Upvotes: 4

Related Questions