How to select data of all months of user selected year even its empty

Question

I have 2 tables, posbila and posbilb. The field bill_date (store with dateTime format) is in table posbila and field Qty in posbilb.

I have tried the follow query but the months with empty data wont be shown

SELECT
    posbilb.stkcode,
    SUM(posbilb.qty),
    date_format(posbila.bill_date, '%m/%y')

FROM
    posbila,posbilb
WHERE
    posbila.bill_no = posbilb.RECEIVE_ID

GROUP BY 
    1, 3

If user select year 2015 I want the following result:

Month | Qty
Janury | 154
February | 00
March | 123
April | 00
May | 00
June | 60
July | 00
August | 99
September | 00
October | 00
November | 10
December | 00

If your tables really don't have the missing dates, then you may have to use a calendar table to include this missing data. — Tim Biegeleisen

P.Salmon P.Salmon · Accepted Answer · 2016-07-20T08:36:53

If you don't have a dates table and don't have permission to create one you could us union. For example given

select * from tbl_loanledger;
+------+-------------+---------------+---------+---------+--------------+---------+
| id   | borrower_id | loanmaster_id | payment | balance | date_created | deleted |
+------+-------------+---------------+---------+---------+--------------+---------+
|    1 |           4 |             1 |      50 |     250 | 2016-05-28   |       0 |
|    2 |           1 |             2 |      20 |      80 | 2016-05-25   |       0 |
|    3 |           1 |             2 |      30 |      50 | 2016-06-01   |       0 |
|    4 |           2 |             3 |     100 |     400 | 2016-06-09   |       0 |
|    5 |           2 |             3 |      50 |     350 | 2016-06-10   |       0 |
|    6 |           1 |             4 |      50 |     200 | 2016-06-16   |       0 |
|    7 |           1 |             7 |      50 |     250 | 2016-06-16   |       1 |
+------+-------------+---------------+---------+---------+--------------+--------+

set @yyyy = '2016';

select      date_format(s.date_created, '%m/%y') 'mm/yy', sum(s.payment) amount
from
(
select  1 as srce,ll.payment payment, ll.date_created date_created from tbl_loanledger ll
union select 2 ,0, concat(@yyyy,'-01-31') union select 2, 0, concat(@yyyy,'-02-01') union select 2, 0, concat(@yyyy,'-03-31')
union select 2, 0, concat(@yyyy,'-04-30') union select 2, 0, concat(@yyyy,'-05-31') union select 2, 0, concat(@yyyy,'-06-30')
union select 2, 0, concat(@yyyy,'-07-31') union select 2, 0, concat(@yyyy,'-07-31') union select 2, 0, concat(@yyyy,'-09-30')
union select 2, 0, concat(@yyyy,'-10-31') union select 2, 0, concat(@yyyy,'-09-30') union select 2, 0, concat(@yyyy,'-12-31') 
) s 
group       by 1

results in

+-------+--------+
| mm/yy | amount |
+-------+--------+
| 01/16 |      0 |
| 02/16 |      0 |
| 03/16 |      0 |
| 04/16 |      0 |
| 05/16 |     70 |
| 06/16 |    230 |
| 07/16 |      0 |
| 09/16 |      0 |
| 10/16 |      0 |
| 12/16 |      0 |
+-------+--------+

How to select data of all months of user selected year even its empty

1 Answers