How to show all months in year regardless if there is a data per month in MySQL

Question

I have a table view_remit with a list of ID and their respective total_PBR every first of the month(E_FROM) till the end of the month (E_TO).

|  ID  |  E_From  |   E_To   |Total_PBR|
|  01  |01/01/2016|03/31/2016|  1000   |
|  01  |08/01/2016|08/31/2016|  1000   |
|  02  |01/01/2016|01/31/2016|  3000   |
|  02  |02/01/2016|02/29/2016|  3000   |

What i want is to select all months with and without payment per ID. Like this example below.

|  ID  |  E_From  |   E_To   |Total_PBR|
|  01  |01/01/2016|01/31/2016|  1000   |
|  01  |02/01/2016|02/29/2016|  1000   |
|  01  |03/01/2016|03/31/2016|  1000   |
|  01  |04/01/2016|04/30/2016|    0    |
|  01  |05/01/2016|05/31/2016|    0    |
|  01  |06/01/2016|06/30/2016|    0    |
|  01  |07/01/2016|07/31/2016|    0    |
|  01  |08/01/2016|08/31/2016|  1000   |
|  01  |09/01/2016|09/30/2016|    0    |
|  01  |10/01/2016|10/31/2016|    0    |
|  01  |11/01/2016|11/30/2016|    0    |
|  01  |12/01/2016|12/31/2016|    0    |

I used this code from another question here MySQL monthly Sale of last 12 months including months with no Sale

SELECT ID, 
IF( MONTH( E_From ) =  1, Total_PBR, 0 ) AS  'Jan', 
IF( MONTH( E_From ) =  2, Total_PBR, 0 ) AS  'Feb', 
IF( MONTH( E_From ) =  3, Total_PBR, 0 ) AS  'Mar', 
IF( MONTH( E_From ) =  4, Total_PBR, 0 ) AS  'Apr', 
IF( MONTH( E_From ) =  5, Total_PBR, 0 ) AS  'May', 
IF( MONTH( E_From ) =  6, Total_PBR, 0 ) AS  'Jun', 
IF( MONTH( E_From ) =  7, Total_PBR, 0 ) AS  'Jul', 
IF( MONTH( E_From ) =  8, Total_PBR, 0 ) AS  'Aug', 
IF( MONTH( E_From ) =  9, Total_PBR, 0 ) AS  'Sep', 
IF( MONTH( E_From ) =  10, Total_PBR, 0 ) AS 'Oct', 
IF( MONTH( E_From ) =  11, Total_PBR, 0 ) AS 'Nov', 
IF( MONTH( E_From ) =  12, Total_PBR, 0 ) AS 'Dec', year(E_FROM) AS Year,
SUM(Total_PBR ) AS total_yearly
FROM view_remit
GROUP BY PEN, YEAR( E_From )
LIMIT 0 , 30

It worked but it doesn't count some of the months' total_PBR correctly. Any help is appreciated.

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Aug 30 '16 at 08:13

score 0 · Answer 1 · answered Aug 30 '16 at 09:29

Try this one :

SELECT ID, 
SUM(IF( MONTH( E_From ) =  1, Total_PBR, 0 )) AS  'Jan', 
SUM(IF( MONTH( E_From ) =  2, Total_PBR, 0 )) AS  'Feb', 
SUM(IF( MONTH( E_From ) =  3, Total_PBR, 0 )) AS  'Mar', 
SUM(IF( MONTH( E_From ) =  4, Total_PBR, 0 )) AS  'Apr', 
SUM(IF( MONTH( E_From ) =  5, Total_PBR, 0 )) AS  'May', 
SUM(IF( MONTH( E_From ) =  6, Total_PBR, 0 )) AS  'Jun', 
SUM(IF( MONTH( E_From ) =  7, Total_PBR, 0 )) AS  'Jul', 
SUM(IF( MONTH( E_From ) =  8, Total_PBR, 0 )) AS  'Aug', 
SUM(IF( MONTH( E_From ) =  9, Total_PBR, 0 )) AS  'Sep', 
SUM(IF( MONTH( E_From ) =  10, Total_PBR, 0 )) AS 'Oct', 
SUM(IF( MONTH( E_From ) =  11, Total_PBR, 0 )) AS 'Nov', 
SUM(IF( MONTH( E_From ) =  12, Total_PBR, 0 )) AS 'Dec', 
year(E_FROM) AS Year,
FROM view_remit
GROUP BY ID, YEAR( E_From )

if you have only one data record in each month, ignore SUM() function

sisanared · Answer 2 · 2017-03-07T12:00:15.293

You can refer to this query and modify it for your purpose. I have created a test table with the following data:

select * from test;
+----+---------------------+--------+
| id | pay_date            | salary |
+----+---------------------+--------+
|  1 | 2015-01-01 00:00:00 |   5000 |
|  2 | 2015-02-01 00:00:00 |   6000 |
|  3 | 2015-05-01 00:00:00 |   5000 |
|  4 | 2015-09-01 00:00:00 |   8000 |
+----+---------------------+--------+

Now run the following query:

SELECT months.monthname, coalesce(payments.salary, 0) FROM
(
  SELECT 'January'   monthname, 1  month_num UNION 
  SELECT 'February'  monthname, 2  month_num UNION 
  SELECT 'March'     monthname, 3  month_num UNION 
  SELECT 'April'     monthname, 4  month_num UNION 
  SELECT 'May'       monthname, 5  month_num UNION 
  SELECT 'June'      monthname, 6  month_num UNION 
  SELECT 'July'      monthname, 7  month_num UNION 
  SELECT 'August'    monthname, 8  month_num UNION 
  SELECT 'September' monthname, 9  month_num UNION 
  SELECT 'October'   monthname, 10 month_num UNION
  SELECT 'November'  monthname, 11 month_num UNION 
  SELECT 'December'  monthname, 12 month_num
) months
LEFT JOIN(
  SELECT monthname (pay_date) monthname, salary FROM test
) payments
ON (months.monthname=payments.monthname)
ORDER BY months.month_num;

which will show these results:

+-----------+------+
| monthname | pay  |
+-----------+------+
| January   | 5000 |
| February  | 6000 |
| March     |    0 |
| April     |    0 |
| May       | 5000 |
| June      |    0 |
| July      |    0 |
| August    |    0 |
| September | 8000 |
| October   |    0 |
| November  |    0 |
| December  |    0 |
+-----------+------+
12 rows in set (0.00 sec)

How to show all months in year regardless if there is a data per month in MySQL

2 Answers2