CODEWITHSUNDEEP
mysql
yas
yas

Reputation: 67

MYSQL select sum by months including with null values

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 enter image description here

below the source table enter image description here

Upvotes: 1

Views: 461

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You don't need the GROUP BY clause in the subquery. Your query should 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;

Upvotes: 2

Related Questions