mysql pivot not counting Date columns

Question

I hope someone can help me see through this query which has been working on another database but now fails.

The below is based on a pivot table with source on left and months across left to right, all works fine except one column which just refuses to be counted!

Count(l.CompDate) As Submitted outputs the same number as Count(l.id) As Leads, the l.ComDate field is a date based column, if there column has a date in it should be counted but its not, it just counts all records.

Can anyone spot where I am going wrong? I have been on this for days.

Select *,   If(q.Source Is Null, 1, 0) As remove From  
(Select l.Source,      
Sum(Month(l.CompDate) = 1) As Jan, 
Sum(Month(l.CompDate) = 2) As Feb,
Sum(Month(l.CompDate) = 3) As Mar,       
Sum(Month(l.CompDate) = 4) As Apr,      
Sum(Month(l.CompDate) = 5) As May,       
Sum(Month(l.CompDate) = 6) As Jun,       
Sum(Month(l.CompDate) = 7) As Jul,       
Sum(Month(l.CompDate) = 8) As Aug,       
Sum(Month(l.CompDate) = 9) As Sep,       
Sum(Month(l.CompDate) = 10) As Oct,       
Sum(Month(l.CompDate) = 11) As Nov,       
Sum(Month(l.CompDate) = 12) As Decb,
Count(l.id) As Leads,
Count(l.CompDate) As Submitted,
ROUND(Count(l.id)*100 / (Count(l.CompDate)), 2) As Conversion
From       tbl_lead l     
Where       Year(l.LeadDate) = 2015     
Group By  l.Source With Rollup) q

Answer 1

The expression:

Count(l.CompDate) As Submitted,

Does exactly what you are expressing. It is counting the non-NULL values of l.CompDate.

It is unclear what you want. If you want the number of distinct date values, then one of the following would work:

Count(distinct l.CompDate) As Submitted,

Count(distinct date(l.CompDate)) As Submitted,