SQL query to select previous 14 months where add amount ‘0’ if any month is missing

I have create this query to select the date, amount for previous 14 months, In this case the month is missing if no record is present for that month, Please suggest me a solution to add zero and month if that paticular month is missing.

SELECT dtSubscriptionRenewalDate, 
DATE_FORMAT(dtSubscriptionRenewalDate, "%b %Y") AS month,     
SUM(intPaymentAmount) as total 
FROM `tbl_pi_payment` 
WHERE strCurrencyCode = 'USD' 
and dtSubscriptionRenewalDate <= NOW() 
and dtSubscriptionRenewalDate >= Date_add(Now(),interval - 14 month) 
group by month  
ORDER BY `tbl_pi_payment`.`dtSubscriptionRenewalDate`  ASC;

You can view from the results, That NOv 2018 is missing.

Answer

Create an ad-hoc table with select/union of the possible months, then left join your table from that, selecting only the records for the given month. For convenience, compute the date range from the number of months ago (but you could just use the more complicated expressions for start date and end date in the join instead).

select
    min(dtSubscriptionRenewalDate) as min_renewal_date,
    date_format(date_sub(now(), interval months_ago month), "%b %Y") as month,
    coalesce(sum(intPaymentAmount),0) as total
from (
    select
        months_ago,
        date(date_format(date_sub(now(), interval months_ago month), "%Y-%m-01")) start_date,
        date(date_format(date_sub(now(), interval months_ago-1 month), "%Y-%m-01")) end_date
    from (select 0 months_ago union select 1 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 union select 13 union select 14 order by months_ago) months_ago
) date_range
left join tbl_pi_payment on
    dtSubscriptionRenewalDate >= start_date and
    dtSubscriptionRenewalDate < end_date and
    dtSubscriptionRenewalDate <= now() and
    strCurrencyCode = 'USD'
group by months_ago desc;

This will report NULL as the renewal date for months with no records, and the minimum renewal date found for months that do have records. Not sure what exactly you want there.