php - SELECT COUNT() in a left join table mysql -

php - SELECT COUNT() in a left join table mysql -

i attempting count number of new leads , number of closed leads in given month. query below returns month array correctly, counts 1 if there leads or closes in months, instead of actual number happened in month. ideas doing wrong?

query:

$productsql = "select months.monthnum monthnum, count(contacts.lead_date) lead_date_month, count(contacts.close_date) close_date_month, contacts.rep rep months left bring together contacts on months.monthnum = month(contacts.lead_date) , months.monthnum = month(contacts.close_date) , contacts.compid='$compid' , year(contacts.lead_date) = '$year' , year(contacts.close_date) = '$year' , contacts.rep = '$rep' grouping months.monthnum order months.monthnum asc"; $productresult = mysql_query($productsql, $link) or die(mysql_error()); $label[] = $productrow['monthnum']; $lead[] = $productrow['lead_date_month']; $close[] = $productrow['close_date_month']; }; echo "[".json_encode($label).",".json_encode($lead).",".json_encode($close)."]";

result:

[["january","february","march","april","may","june","july","august","september","october","november","december"], ["0","0","0","0","0","0","0","0","1","1","0","0"], ["0","0","0","0","0","0","0","0","1","1","0","0"]]

**please don't chastise me using mysql. dumping project finished.

the way build join status results records month(contacts.lead_date) = month(contacts.close_date).

to prepare , actual count suggest splitting statement 2 queries:

select a.monthnum, a.lead_date_month, b.close_date_month, b.close_date_month / a.lead_date_month ( -- count number of new leads select months.monthnum monthnum, count(*) lead_date_month, contacts.rep rep months left bring together contacts on months.monthnum = month(contacts.lead_date) , contacts.compid='$compid' , year(contacts.lead_date) = '$year' , contacts.rep = '$rep' grouping months.monthnum order months.monthnum asc ) bring together ( -- count number of closed leads select months.monthnum monthnum, count(*) close_date_month, contacts.rep rep months left bring together contacts on months.monthnum = month(contacts.close_date) , contacts.compid='$compid' , year(contacts.close_date) = '$year' , contacts.rep = '$rep' grouping months.monthnum order months.monthnum asc ) b on a.monthnum = b.monthnum

and mysql isn't bad. mariadb better, right :-)

php mysql count left-join