How to group by month in MySQL and get results even for months which has no value with them

Question

I have payment data table (MySQL) to record all the payments. I want to group all the payments by date. Actually I want to group them by month to analyze what's the income they get for a specific month. If there are no payments in a specific month, I need that as 0.

Here is my SQL code for that.

SELECT SUM(p.sub_total) as sold_income , month(p.invoice_date) as income_month ,MONTHNAME(p.invoice_date) as month_name, YEAR(p.invoice_date) as income_year FROM payment p where (p.invoice_date BETWEEN '2018-01-01' AND '2018-08-01') GROUP BY DATE_FORMAT(p.invoice_date, '%Y%m')

From this code, I get the following output :

I searched between '2018-01-01' AND '2018-08-01'. So there should be 7 months and 7 results. If there are no payments for month no 1 (January) , I want the sold_income = 0;

How do I do that ? Please help me....

Look online for calendar table, there are many examples to be found. — HoneyBadger, Sep 01 '18 at 14:09
@HoneyBadger Can you please be more specific brother ? I'm new to grouping and sorting using SQL. — Chanaka De Silva, Sep 01 '18 at 14:17

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-01T14:26:37.443

1

generate date and join with this your table and project your result

  SELECT SUM(p.sub_total) as sold_income , month(C_date ) as income_month ,MONTHNAME(C_date ) as month_name, YEAR(C_date ) as income_year from    

           (
                select * from 
                (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) C_date from
                 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
                 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
                 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
                 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
                 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
                where C_date between '2017-08-01' and '2018-12-31'
                ) All_date
    left join  payment p on on date(p.invoice_date )=All_date.C_date
    where C_date 
    BETWEEN '2017-08-01' AND '2018-08-01'
    GROUP BY income_month ,month_name,income_year

edited Sep 01 '18 at 14:26

answered Sep 01 '18 at 14:11

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks bro. Is this possible if I want to search between '2017-08-01' AND '2018-08-01'. Like 2 years ? – Chanaka De Silva Sep 01 '18 at 14:21
@ChanakaDeSilva yap possible just change where C_date between '2018-01-01' and '2018-12-31' in subquery of date generation and if it helps please accept answer as a SO appreciation – Zaynul Abadin Tuhin Sep 01 '18 at 14:22
And your code is not working bro :-( – Chanaka De Silva Sep 01 '18 at 14:22
@ChanakaDeSilva it should work show me error – Zaynul Abadin Tuhin Sep 01 '18 at 14:23
I see a NULL column at the top.. Please check this : https://ibb.co/khsdVK – Chanaka De Silva Sep 01 '18 at 14:24
@ChanakaDeSilva check please now i edited my answer – Zaynul Abadin Tuhin Sep 01 '18 at 14:26
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/179246/discussion-between-zaynul-abadin-tuhin-and-chanaka-de-silva). – Zaynul Abadin Tuhin Sep 01 '18 at 14:28
#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'All_date.C_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Chanaka De Silva Sep 03 '18 at 06:45
I get this error :-( – Chanaka De Silva Sep 03 '18 at 06:45
@ChanakaDeSilva what error you got? – Zaynul Abadin Tuhin Sep 03 '18 at 06:49
#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'All_date.C_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Chanaka De Silva Sep 03 '18 at 06:50
https://ibb.co/j58XSz – Chanaka De Silva Sep 03 '18 at 06:51
Did you get a solution ? – Chanaka De Silva Sep 03 '18 at 07:09
i have sent you already query did you check – Zaynul Abadin Tuhin Sep 03 '18 at 07:11
Yeah i checked. The updated answer you added yesterday. But it's getting an error on my MySQL database in Linux server. Server type: MySQL Server version: 5.7.23-0ubuntu0.16.04.1 - (Ubuntu) Protocol version: 10 – Chanaka De Silva Sep 03 '18 at 07:17
@ChanakaDeSilva the answer that is here in my post have it thrown any error? – Zaynul Abadin Tuhin Sep 03 '18 at 07:20
@ChanakaDeSilva i knocked you in chat but you didn't repy??? – Zaynul Abadin Tuhin Sep 03 '18 at 07:21

How to group by month in MySQL and get results even for months which has no value with them

1 Answers1