mysql - Count, SUM, LEFT JOIN and GROUP BY in query not working right -

mysql - Count, SUM, LEFT JOIN and GROUP BY in query not working right -

i have tried few things can't seem figure out what's causing problem. when remove totalhours part, query works fine. it, displays right number of hours wrong number of jobs, selected , winners.

could please tell me doing wrong?

thanks in advance.

here query;

select crmcandidate.candidateid, crmcandidate.candidatename, count(distinct crmjoin.joinid) jobs, sum(if(crmjoin.joinextra = 'select', 1, 0)) selected, sum(if(crmjoin.joinextra = 'winner', 1, 0)) winner, round(sum(crmdays.total)) totaldays crmcandidate left bring together crmjoin on (crmjoin.joinchild = crmcandidate.candidateid) left bring together crmjob on (crmjob.jobid = crmjoin.joinparent) left bring together crmdays on (crmdays.daycandidateid = crmjoin.joinchild) crmdays.dayjobid = crmjob.jobid , crmdays.daycandidateid = crmcandidate.candidateid grouping crmcandidate.candidateid order desc limit 100

try 1 :

select crmcandidate.candidateid, crmcandidate.candidatename, count(distinct crmjoin.joinid) jobs, sum(case when crmjoin.joinextra = 'select' 1 else 0 end) selected, sum(case when crmjoin.joinextra = 'winner' 1 else 0 end) winner, round(sum(crmdays.total)) totaldays crmcandidate left bring together crmjoin on crmjoin.joinchild = crmcandidate.candidateid left bring together crmjob on crmjob.jobid = crmjoin.joinparent inner bring together crmdays on crmdays.daycandidateid = crmcandidate.candidateid , crmdays.dayjobid = crmjob.jobid grouping crmcandidate.candidateid, crmcandidate.candidatename order candidateid desc limit 100

mysql count group-by sum left-join