dpatel
09-23-2014, 01:09 AM
I have following data values as an example from the table.
TID
Date
MID
1
2014-02-01
5
2
2014-03-15
6
3
2014-02-26
5
4
2014-05-03
5
5
2014-04-19
6
Now i am trying to show the records group by month and MID if the data doesn't exists on specific month of MID. I tried using UNION to display
missing months but not getting expected results like below...
MID
Month
count
5
1
0
5
2
2
5
3
0
5
4
0
5
5
1
6
1
0
6
2
0
6
3
1
6
4
1
6
5
0
And here is my query so far... Hope someone can help to get above results.
SELECT Months.id `month` ,
COUNT(my_table.date) `count`
FROM
(
SELECT 1 ID UNION SELECT 2 ID UNION SELECT 3 ID UNION SELECT 4 ID
UNION
SELECT 5 ID UNION SELECT 6 ID
) Months
LEFT JOIN my_table on Months.id=month(my_table.date)
GROUP BY Months.id
ORDER BY Months.id ASC
TID
Date
MID
1
2014-02-01
5
2
2014-03-15
6
3
2014-02-26
5
4
2014-05-03
5
5
2014-04-19
6
Now i am trying to show the records group by month and MID if the data doesn't exists on specific month of MID. I tried using UNION to display
missing months but not getting expected results like below...
MID
Month
count
5
1
0
5
2
2
5
3
0
5
4
0
5
5
1
6
1
0
6
2
0
6
3
1
6
4
1
6
5
0
And here is my query so far... Hope someone can help to get above results.
SELECT Months.id `month` ,
COUNT(my_table.date) `count`
FROM
(
SELECT 1 ID UNION SELECT 2 ID UNION SELECT 3 ID UNION SELECT 4 ID
UNION
SELECT 5 ID UNION SELECT 6 ID
) Months
LEFT JOIN my_table on Months.id=month(my_table.date)
GROUP BY Months.id
ORDER BY Months.id ASC