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
The expression:
Does exactly what you are expressing. It is counting the
non-NULL
values ofl.CompDate
.It is unclear what you want. If you want the number of distinct date values, then one of the following would work: