How to add month name with 0 total if data not exist in mysql?

Question

I need to fetch data of the user's from created_date to till current data by month name and year with each month's total price.

Try 1:

SELECT MONTHNAME(start_time) month, YEAR(start_time) year, SUM(price) total
FROM table t1
WHERE t1.id= 33
GROUP BY YEAR(start_time), MONTH(start_time);

Output:

month           year    total
July            2019    360
September       2019    2160
October         2019    360

Expected output:

All month name and total will be 0 if data not exist. 

month           year    total
Jan             2018    0
Feb             2018    0
...
Dec             2018    0
Jan             2019    0
Feb             2019    0
Mar             2019    0
Apr             2019    0
May             2019    0
Jun             2019    0
July            2019    360
Aug             2019    0
Sep             2019    2160
Oct             2019    360
Nov             2019    0
Dec             2019    0

After some RND I found one way and I have also tried that but now works.

Try 2:

 SELECT IFNULL(SUM(ri.price),0) AS total, m.month
 FROM (
       SELECT 'Jan' AS MONTH
       UNION SELECT 'Feb' AS MONTH
       UNION SELECT 'Mar' AS MONTH
       UNION SELECT 'Apr' AS MONTH
       UNION SELECT 'May' AS MONTH
       UNION SELECT 'Jun' AS MONTH
       UNION SELECT 'Jul' AS MONTH
       UNION SELECT 'Aug' AS MONTH
       UNION SELECT 'Sep' AS MONTH
       UNION SELECT 'Oct' AS MONTH
       UNION SELECT 'Nov' AS MONTH
       UNION SELECT 'Dec' AS MONTH
      ) AS m
LEFT JOIN table_u pu 
ON MONTH(STR_TO_DATE(CONCAT(pu.created_date, '2019'),'%M %Y')) = MONTH(pu.created_date)
AND YEAR(pu.created_date) = '2019'
LEFT JOIN table ri 
ON ri.id = pu.id
GROUP BY m.month
ORDER by 1+1;

Here is my refrence link.

Can anyone help me to fix this issue?

Thanks in advance.

Answer 1

Finally, I got the correct output which I want.

select
DATE_FORMAT(m1, '%M - %Y')

from
(
  select
  ('2013-07-23')
  +INTERVAL m MONTH as m1
  from
  (
     select @rownum:=@rownum+1 as m from
     (select 1 union select 2 union select 3 union select 4) t1,
     (select 1 union select 2 union select 3 union select 4) t2,
     (select 1 union select 2 union select 3 union select 4) t3,
     (select 1 union select 2 union select 3 union select 4) t4,
     (select @rownum:=-1) t0
  ) d1
) d2
  where m1<=NOW()
  order by m1

Answer 2

Keep using LEFT JOIN (but only once) with a integer generator upto 12 (instead of month names, also easy using within the ORDER BY clause ), since you are able to get the month names by monthname() function. From your second query, I considered the current year. So, use :

SET @year=YEAR(NOW());

SELECT 
      MONTHNAME(STR_TO_DATE(concat(YEAR(NOW()),',',m.month,',1'),"%Y,%m,%d")) as month,
      @year as year , SUM( COALESCE( pu.price, 0) ) as total
  FROM (
        SELECT rn as month 
          FROM
          (
           SELECT @rn := if(@i = @rn, @rn + 1, 1) as rn,
                  @i := @i+1
             FROM information_schema.character_sets
             JOIN (SELECT @i := 0, @rn := 0) as q_iter
            LIMIT 12
          ) q
        ) AS m
   LEFT JOIN table_u pu ON MONTH(pu.start_time) = m.month
    AND YEAR(pu.start_time) = @year
    AND ID = 33
  GROUP BY m.month
  ORDER by m.month;

Demo 1

Edit ( From the beginning of the year 2018 to the end of the current year ) :

SELECT MONTHNAME(
         STR_TO_DATE(
           CONCAT(m.year,',',if(mod(m.month,12)=0,12,mod(m.month,12)),',1'),"%Y,%m,%d")
           ) 
       as month, 
       year, 
       SUM( CASE WHEN YEAR(pu.start_time) = m.year AND MONTH(pu.start_time) = m.month
                 THEN 
                      COALESCE( pu.price, 0) 
                 ELSE
                      0
            END ) as total 
  FROM
  (
       SELECT @i := if( @i = 12 , 1 , @i+1) as month,
              @j := @j + 1,    
              @k:= if( @i = 1 and @j > 12, @k - 1, @k ) as year
             FROM information_schema.character_sets
         JOIN (SELECT @i := 0, @j := 0, @k:=YEAR(now())) as q_iter
   ) m
   LEFT JOIN table_u pu ON MONTH(pu.start_time) = m.month    
    AND ID = 33
  GROUP BY month, year
 HAVING year >= ( SELECT MIN( YEAR(start_time) ) FROM table_u  )
  ORDER BY year, m.month;

Demo 2

Answer 3

You can try this:

SELECT to_char(start_time,'month')MONTH,to_char(start_time,'yyyy')YEAR,SUM(price)total 
FROM   TABLE_NAME
GROUP BY to_char(start_time,'month'),to_char(start_time,'yyyy')

Answer 4

Creating nonexistent year-month pairs by using user variables:

SELECT monthname(str_to_date(concat_ws(',',ym.month,'01,01'),'%m,%d,%y')) month
     , ym.year year 
     , sum(price)
  FROM table1 t1
    RIGHT JOIN( SELECT @year  := if(@month=12, @year+1, @year   ) year
                    , @month := if(@month=12, 1      , @month+1) month
                 FROM table1
                    , ( SELECT @startYear := min(start_time)
                             , @endYear   := year(now())
                             , @month := 12
                             , @year  := min(start_time)-1
                          FROM table1
                      ) t
                 WHERE (@year,@month) < (@endYear,12)
             ) ym
      ON    ym.year  = year(t1.start_time)
        AND ym.month = month(t1.start_time)
  GROUP BY year(t1.start_time)
         , month(t1.start_time)

The ym derived table is to provide year-month pairs starting on min year in table1 upto current year. The innermost SELECT is for variables initialization.

Answer 5

If you have data in your table for all months, but just not for that id, then you can switch to conditional aggregation:

SELECT MONTHNAME(start_time) as month, YEAR(start_time) as year, 
       SUM(CASE WHEN t1.id = 33 THEN price ELSE 0 END) as total
FROM table t1
GROUP BY YEAR(start_time),MONTHNAME(start_time)
ORDER BY MIN(start_time);