How to get results for all month in Mysql-CodePudding

I have a tbl_order in my database.

In this table, I have many orders for users (each record have a timestamp value)

The problem is I need to get orders counted for the current year for each month.

Example 1 => 200 2 => 12000 3 => 0 4 => 0 ... 12 => 100

I cannot find a query that gets results for a month does not have any record (return 0 if not founds any records)

Actually, my query return records count only for existing records for that date

I cannot do it and I have to use PHP code for set 0 for some months

CodePudding user response：

Using php code to set 0 for some months is the best way to do it. It's almost always better to deal with display issues in the client.

But if you really need to do it in the sql for some reason, you left join your query to a table of months:

select mo, coalesce(orders_count,0) as orders_count
from (select 1 mo union all select 2 union all select 3 union all select 4 union all
      select 5 union all select 6 union all select 7 union all select 8 union all
      select 9 union all select 10 union all select 11 union all select 12
) months
left join (
   ... your query that returns mo and orders_count for all months with orders
) months_with_orders using (mo)

Or using a recursive common table expression:

with recursive months as (select 1 mo union all select mo 1 from months where mo < 12)
select mo, coalesce(orders_count,0) as orders_count
from months 
left join (
   ... your query that returns mo and orders_count for all months with orders
) months_with_orders using (mo)