How to select from mysql that group by month in one year and sum value in a month?

Question

received_amount | updated_at
----------------------------
4000            | 2020-11-02
2000            | 2020-11-03
1000            | 2020-12-02
1000            | 2020-12-04
3000            | 2021-01-02
2000            | 2020-01-02

This is my database table named "payment" structured and data on it.

Now I want to display something like below mentioned table data in my php web page when user select the year from calendar (I've use Zebra date picker for this). Zebra date picker gives the year.

In this scenario user select the year 2020 and it's should show the values as below.

Year  | Month   | Monthly Total
----------------------------
2020  | November| 6000
2020  | December| 2000

If the user selects the year then why do you have in your expected results rows of more than 1 year? — forpas, Jan 16 '21 at 19:48
Related: [mysql select sum group by date](https://stackoverflow.com/q/937652/2943403) — mickmackusa, Aug 17 '22 at 23:00

azibom · Answer 1 · 2021-01-16T19:59:50.447

1

you can use group by like this in your query

GROUP BY EXTRACT(YEAR FROM updated_at), EXTRACT(MONTH FROM updated_at)

and then select like this

SELECT EXTRACT(YEAR FROM updated_at) as Year,
       EXTRACT(MONTH FROM updated_at) as Month,
       SUM(received_amount) as Monthly_Total

edited Jan 16 '21 at 19:59

answered Jan 16 '21 at 19:56

azibom

1,769
1
7
21

Your code also helped me. Thank you very much. – Nipun Tharuka Jan 17 '21 at 03:10

score 1 · Accepted Answer · answered Jan 16 '21 at 20:00

You can do:

select year, month, monthly_total
from (
  select
    extract(year from updated_at) as year,
    extract(month from updated_at) as m,
    monthname(updated_at) as month,
    sum(receive_amount) as monthly_total
  from t
  where extract(year from updated_at) = 2020
  group by year, month, m
) x
order by year, m

Result:

 year  month     monthly_total 
 ----- --------- ------------- 
 2020  January   2000          
 2020  November  6000          
 2020  December  2000

See running example at DB Fiddle.

Hi. The query is working as expected in my database table. But how can I get the result into a table in my php page? I have tried but gives errors. Kindly help me. — Nipun Tharuka, Jan 17 '21 at 02:36

How to select from mysql that group by month in one year and sum value in a month?

2 Answers2