I had a problem. I have the data like below:
-----------------------
| last_login_dt |
-----------------------
| 2015-08-11 08:06:36 |
| 2015-06-10 22:06:43 |
| 2015-06-11 08:06:58 |
| 2015-09-11 08:06:45 |
-----------------------
So far, I managed to count this kind of data and turn into this using below statement:
SELECT DATE_FORMAT(last_login_dt,'%m/%Y') as `month`,
count(last_login_dt) as `total_visits` from public_user
WHERE DATE_FORMAT(last_login_dt,'%Y') = YEAR(CURDATE())
group by DATE_FORMAT(last_login_dt,'%m/%Y')
order by `month` asc;
----------------------------
| month | total_visits |
----------------------------
| 06/2015 | 2 |
| 08/2015 | 1 |
| 09/2015 | 1 |
----------------------------
The problem is, how to turn the result like below. Instead this table only have 4 rows of data, how to create a jan, feb, mar, apr ... row with total_visit = 0:
---------------------
| Month | Total |
---------------------
| Jan | 0 |
| Feb | 0 |
| Mar | 0 |
| Apr | 0 |
| May | 0 |
| Jun | 2 |
| Jul | 0 |
| Aug | 1 |
| Sep | 1 |
| Oct | 0 |
| Nov | 0 |
| Dis | 0 |
---------------------
I use your own query because it's working and I modified it a bit. Consider the query below:
It results in:
I first created the table that displays the months and its total_visits (which is obviously 0). Then I unite your query to the derived table I created. I also added the
month_num
field for the sole purpose of sorting the data by month although it's not shown in the final result.