CODEWITHSUNDEEP
mysqlsql
Ramadhani Baharzah
Ramadhani Baharzah

Reputation: 77

How to show months if it has no record and force it to zero if null on MySQL

i have an orders table, and i need to fetch the orders record by month. but i have terms if there is no data in a month it should still show the data but forcing to zero like this:

enter image description here

what i have done is using my query:

select sum(total) as total_orders, DATE_FORMAT(created_at, "%M") as date 
from orders 
where is_active = 1
AND tenant_id = 2
AND created_at like '%2021%'
group by DATE_FORMAT(created_at, "%m")

but the result is only fetched the existed data:

enter image description here

can anyone here help me to create the exactly query?

Thank you so much

Upvotes: 0

Views: 4127

Answers (2)

Ramadhani Baharzah
Ramadhani Baharzah

Reputation: 77

Thanks all for the answers & comments i really appreciate it.

i solved it by create table helper for static months then use union and aliasing, since i need the months in indonesia, i create case-when function too.

so, the query is like this:

SELECT total_orders,
       (CASE date WHEN 01 THEN 'Januari'
                  WHEN 02 THEN 'Februari'
                  WHEN 03 THEN 'Maret'
                  WHEN 04 THEN 'April'
                  WHEN 05 THEN 'Mei'
                  WHEN 06 THEN 'Juni'
                  WHEN 07 THEN 'Juli'
                  WHEN 08 THEN 'Agustus'
                  WHEN 09 THEN 'September'
                  WHEN 10 THEN 'Oktober'
                  WHEN 11 THEN 'November'
                  WHEN 12 THEN 'Desember'
                  ELSE date END                    ) AS date
FROM (SELECT SUM(total)                    AS total_orders, 
             DATE_FORMAT(created_at, "%m") AS date 
      FROM orders 
      WHERE is_active = 1
        AND tenant_id = 2
        AND created_at like '%2021%'
      GROUP BY DATE_FORMAT(created_at, "%m")

      UNION

      SELECT 0    AS total_orders, 
             code AS date
      FROM quantum_default_months             ) as Q
GROUP BY date

I still don't know if this query is fully correct or not, but I get my exact result.

enter image description here

cmiiw. thanks all

Upvotes: 1

FanoFN
FanoFN

Reputation: 7114

Whenever you're trying to use a value that doesn't exist in the table, one option is to use a reference; whether it's from a table or a query-generated value.

I'm guessing that in terms of date data, the column created_at in table orders may have a complete list all the 12 months in a year regardless of which year.

Let's assume that the table data for orders spans from 2019 to present date. With that you can simply create a 12 months reference table for a LEFT JOIN operation. So:

SELECT MONTHNAME(created_at) mnt FROM orders GROUP BY MONTHNAME(created_at);

You can append that into your query like:

SELECT IFNULL(SUM(total),0) as total_orders, mnt
from (SELECT MONTHNAME(created_at) mnt FROM orders GROUP BY MONTHNAME(created_at)) mn 
LEFT JOIN orders o
ON mn.mnt=MONTHNAME(created_at)
AND is_active = 1
AND tenant_id = 2
AND created_at like '%2021%'
GROUP BY mnt;

Apart from adding the 12 months sub-query and a LEFT JOIN, there are 3 other changes from your original query:

  1. IFNULL() is added to the SUM() operation in SELECT to return 0 if the value is non-existent.
  2. All the WHERE conditions has been switched to ON since remaining it as WHERE will make the LEFT JOIN becoming a normal JOIN.
  3. GROUP BY is using the sub-query generated month (mnt) value instead.

Taking consideration of table orders might not have the full 12 months, you can generate it from query. There are a lot of ways of doing it but here I'm only going to show the UNION method that works with most MySQL version.

SELECT MONTHNAME(CONCAT_WS('-',YEAR(NOW()),mnt,'01')) dt
FROM
(SELECT 1 AS mnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION 
SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) mn

If you're using MariaDB version that supports SEQUENCE ENGINE, the same query above is much shorter:

SELECT MONTHNAME(CONCAT_WS('-',YEAR(NOW()),mnt,'01'))
FROM (SELECT seq AS mnt FROM seq_1_to_12) mn

I'm using MariaDB 10.5 in this demo fiddle however it seems like the month name ordering is based on the name value rather than the month itself so it looks un-ordered. It's in the correct order if it's in MySQL 8.0 fiddle though.

Upvotes: 1

Related Questions