MYSQL select sum by months including with null values-CodePudding

I'm working on a mysql request that make the sum of values by months including those with null values. The request result send only the first line without making the sum operation.

SELECT SUM(IFNULL(t1.sub_total,0)) AS amount, 
       am.a_month AS date
FROM (
        SELECT ifnull(vn.sub_total,0) as sub_total,
               cast(DATE_FORMAT(order_date, '%M') as char) as mdate
        FROM orders_new vn
        WHERE order_status = 1
        AND order_date BETWEEN '2022-01-01' AND '2022-12-31' 
        GROUP BY DATE_FORMAT(order_date, '%M')
      ) t1 
RIGHT OUTER JOIN all_months am on t1.mdate = am.a_month
group by am.a_month
order by a_month_id asc;

result

below the source table

CodePudding user response：

You dont need the GROUP BY clause in sub-query. So correct qury shoud be -

SELECT SUM(IFNULL(t1.sub_total,0)) AS amount,
       am.a_month AS date
  FROM (SELECT ifnull(vn.sub_total,0) as sub_total,
               cast(DATE_FORMAT(order_date, '%M') as char) as mdate
          FROM orders_new vn
         WHERE order_status = 1
           AND order_date BETWEEN '2022-01-01' AND '2022-12-31' 
       ) t1 
 RIGHT OUTER JOIN all_months am on t1.mdate = am.a_month
 GROUP BY am.a_month
 ORDER BY a_month_id asc;