Hello, I have a mysql query which works fine outside of Noloco but not inside and I cant quite work out why. It has unique fields but just displays a few records when there should be 100s.
I’ve tried setting l.id, year and various others to primary with no success.
SELECT
IfNull(l.LeadSource, 'GRAND TOTAL') AS LeadSource,
Year(l.LeadDate) AS Year,
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(MonthName(l.CompDate)) AS Complete,
ROUND(Count(MonthName(l.CompDate)) * 100 / Count(l.id), 0) AS Converted,
l.id AS LeadID
FROM
tbl_lead l
INNER JOIN tbl_clients c ON c.client_id = l.client_id
LEFT JOIN users u ON u.username = c.Adviser
GROUP BY
Year(l.LeadDate),
l.LeadSource
ORDER BY `Year` DESC
