Displaying Zero Valued Months with SQL

Displaying zero valued months with SQL

While not impossible, it's not easy to do this in SQL.

Since it is mostly a presentation issue it would probably be easier to add the missing rows in the client after you have executed the query in your question by iterating over the results and checking for missing months.

SQL How to show '0' value for a month, if no data exists in the table for that month

You can query a table with the values 1-12 and left outer join your result.

Here is a sample using a table variable instead of your query and a CTE to build a table with numbers.

declare @T table
(
  Month int
)

insert into @T values(1)
insert into @T values(1)
insert into @T values(1)
insert into @T values(3)
insert into @T values(3)

;with Months(Month) as
(
  select 1
  union all
  select Month + 1
  from Months
  where Month < 12
)
select M.Month,
       count(T.Month) Count,
       isnull(sum(T.Month), 0) Sum
from Months as M
  left outer join @T as T
    on M.Month = T.Month
group by M.Month

Result:

Month       Count       Sum
----------- ----------- -----------
1           3           3
2           0           0
3           2           6
4           0           0
5           0           0
6           0           0
7           0           0
8           0           0
9           0           0
10          0           0
11          0           0
12          0           0

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

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.

cmiiw.
thanks all

How to group by month and return zero if no value for certain month?

Maybe this it's not the best way to do it, but it will solve your problem. As a quick soution:

SELECT 'January' AS mName, 1 AS mOrder, COALESCE(SUM(amount),0) AS total_num 
FROM income i
WHERE month(i.date) = 1

UNION

SELECT 'February' AS mName, 2 AS mOrder, COALESCE(SUM(amount),0) AS total_num 
FROM income i
WHERE month(i.date) = 2

UNION

...and go on

Show Each Month And Count Or 0 If Null

You can simply add a lookup table with month that will contains 12 records (one for each month)

The structure will be: id, description.

On existing tables you can refers month by id: less space more performance in join.

On your query using left/right join you will always have the missing month.

PS: need only one table to manage this, and adding a simple where condition (or group by condition) where needed for years management

Get mySQL MONTH() to use leading zeros?

Use the following instead:

DATE_FORMAT(`datetime_added`,'%Y-%m')

Explanation:

DATE_FORMAT() function lets you format the date anyway you like using specifiers described in the table below (taken verbatim from documentation). So a format string '%Y-%m' means: "A full year (4 digits), followed by a dash (-), followed by a two-digit month number".

Note that you can specify the language used for day/month names by setting lc_time_names system variable. Extremely useful. Refer to documentation for more details.

SELECT
  Category.name,
  Dates.Year,
  Dates.Month,
  SUM(Data.Value) AS Value
FROM
  Category
CROSS JOIN
  Dates
LEFT JOIN
  Data
    ON  data.CategoryID = Category.ID
    AND data.Year       = Dates.Year
    AND data.Month      = Dates.Month
    AND data.Property  IN ('A1','B1') 
    AND data.Property2 IN ('C1','D1')
WHERE Dates.Year  BETWEEN #### AND ####
  AND Dates.Month BETWEEN #### AND ####
GROUP BY
  Category.name,
  Dates.Year,
  Dates.Month
ORDER BY
  Category.name,
  Dates.Year,
  Dates.Month

;WITH mcte AS (
    SELECT CAST('2016-01-01' as datetime) as MONTH_NAME
    UNION ALL
    SELECT DATEADD(MONTH,1,MONTH_NAME)
    FROM mcte
    WHERE DATEPART(MONTH,MONTH_NAME) < 12
), octe AS (
    SELECT  (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, ORDER_DATE), -1) )) AS MONTH_NAME,
            SUM (o.NET_AMOUNT) AS TOTAL_SALES
    FROM [ORDER] o
    INNER JOIN ORDER_DETAILS od
        ON o.ORDER_ID = od.ORDER_ID
        --AND (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, ORDER_DATE), -1) )) = (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, @Order_month), -1) )) 
    GROUP BY MONTH(o.ORDER_DATE)
)

SELECT  DATENAME(MONTH,m.MONTH_NAME) as MONTH_NAME,
        o.TOTAL_SALES
FROM mcte m
LEFT JOIN octe o
    ON o.MONTH_NAME = DATENAME(MONTH,m.MONTH_NAME)